Cast variable length VARCHAR date/time strings to TIMESTAMP

Database
N/A

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?

1 REPLY
Enthusiast

Re: Cast variable length VARCHAR date/time strings to TIMESTAMP

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