All Forums Database
jdburns2014 2 posts Joined 01/14
23 Jan 2015
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

dnoeth 4628 posts Joined 11/04
24 Jan 2015

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)

Dieter

You must sign in to leave a comment.