All Forums Database
Scomo 1 post Joined 10/15
08 Oct 2015
Cast variable length VARCHAR date/time strings to TIMESTAMP

I'm very familiar with casting a date/time into various formats to timestamp, but I'm hoping there's an EASY way to cast variable length items to TIMESTAMP.
Example:
7/20/2015 16:03
12/20/2015 09:32
Simple CAST as TIMESTAMP:  Error 6760
CAST as TIMESTAMP FORMAT 'mm/dd/yyyyBhh:mi'  : Error 6760 -- It will correctly cast the second data, but fails the first.
Likewise, the data I'm working with has 1 and 2 digit days as well.  
Thoughts?

dins2k2 51 posts Joined 05/13
24 Nov 2015

Hi, This should work. Input Month is always in mm format like "02/23/2015 12:34". I'm just adding "0" to the month and casting it. Tested in 14.10.
 
Select '2/20/2015 16:03' as date1,
cast (
case  
    when strtok(date1, '/', 1) between 1 and 9 then '0'||date1
    else  date1
end as timestamp(0) format 'mm/dd/yyyyBhh:mi') as cast_date;

 
Thanks,
Dinesh

You must sign in to leave a comment.