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.
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
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.