All Forums Database
jmillzz4448 1 post Joined 12/15
07 Dec 2015
CASTing Date and Time to Timestamp while CASTing Time from varchar to TIME
select top 5 CAST (Date AS TIMESTAMP(0)) +(((cast((Time) as TIME(0))) - TIME '00:00:00') HOUR TO SECOND (0)) from tablename

Hi all -
I am slamming my head against a wall on this, and all my googling and searching here has come to naught.
I am trying to cast two seperate columns, Date [DATE] and Time [VARCHAR(6)] into a single TIMESTAMP(0). Here's the code I've tried, and I don't understand why this isn't working. I'm getting a 5407: Invalid Operation for DateTime or Interval as the result.
Any ideas? 
 

Priya01 10 posts Joined 08/14
08 Dec 2015

Hi Jeremy,
Please post sample values for time column.
If the value in your time column is like '044335', then the below code will help you:-
select top 5 CAST (Date AS TIMESTAMP(0)) +(((cast((substr(time,1,2)||':'||substr(time,3,2)||':'||substr(time,4,2)) as TIME(0))) - TIME '00:00:00') HOUR TO SECOND (0)) from tablename
Thanks,
Priya

Fred 1096 posts Joined 08/04
09 Dec 2015

The legacy built-in function TIME has type FLOAT so cast((Time) as TIME(0)) is invalid. Use CURRENT_TIME(0) or just CURRENT_TIME instead of TIME.

You must sign in to leave a comment.