Hi - I have a date time variable in this format 6/2/2010 13:28:44.000000. I would like to drop the milliseconds so it looks like this 6/2/2010 13:28:44 but still maintained as a datetime variable.
I've tried something like this
select cast(DISPOR_DT_TM as timestamp(0) format 'MM/DD/YYYYBHH:MM:SS') as cdate
but I get a DateTime overflow
You can't reduce the precision of a timestamp (blame Standard SQL), so there are two solutions:
- don't use TIMESTAMP(6) for this column if only need TIMESTAMP(0)
- CAST to a string using the TIMESTAMP(0) format and then cast back to TIMESTAMP(0)