CASTing Date and Time to Timestamp while CASTing Time from varchar to TIME

Database

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? 

2 REPLIES
Enthusiast

Re: CASTing Date and Time to Timestamp while CASTing Time from varchar to TIME

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

Teradata Employee

Re: CASTing Date and Time to Timestamp while CASTing Time from varchar to TIME

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.