Converting a date time

Database

Converting a date time

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

from spprthy_curr_bmt

but I get a DateTime overflow

1 REPLY
Junior Contributor

Re: Converting a date time

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)