CODEWITHSUNDEEP
datetimeteradata
John Smith
John Smith

Reputation: 2876

Teradata Create a timestamp from a date and time

Hi,

I am trying to create a bespoke time stamp in teradata. The end result should be last saturday at 14:30. Below is my attempt but im getting an error i was wondering if anyone could help

SELECT CAST(CAST(DATE - ((DATE - DATE '0001-01-07') MOD 7) -1 AS FORMAT 'DD/MM/YYYY') AS CHAR(12)) + ('04:00:00' - TIME '00:00:00' HOUR TO SECOND) ts

Invalid Operation for date or time

Thank you for your time

Upvotes: 0

Views: 845

Answers (1)

dnoeth
dnoeth

Reputation: 60462

You're mixing a Char and an Interval, this is not allowed.

To get the previous Saturday you can find the next sunday and subtract 8 days: 

Next_Day(Current_Date, 'Sun')-8

Now cast this to a Timestamp and add an Interval:

Cast(Next_Day(Current_Date, 'Sun')-8 AS TIMESTAMP(0)) + INTERVAL '14:30' HOUR TO MINUTE

Upvotes: 1

Related Questions