I have values below for which diff field is giving error like
" invalid timestamp" in teradata
Might be it is not doing calculation anymore after exceeding minute(4) value
Could you please help here.
END_TS = 2/2/2018 08:50:49.000000 START_TS = 1/5/2018 17:30:02.000000 SLA_TIME = 23:59:59.000000
select (cast((cast(JRDC.END_TS as timestamp) -Cast(CAST( cast(JRDC.START_TS as DATE FORMAT 'YY/MM/DD') AS TIMESTAMP(0)) + (SLA_TIME - TIME '00:00:00.000000' HOUR TO SECOND) as timestamp) minute(4)) + CAST(1 AS interval minute) as decimal )) -1 as DIFF from base_tbl.xyz
Could you confirm the datatypes in your table ?
timestamp(6) for START_TS and END_TS
time(6) from SLA_TIME
Yes, you have 88 days in difference between your two timestamp, minute(4) is limited to 9999 minutes which is a bit less than 7 days.
What are you trying to achieve precisely ?
This expression is valid :
(END_TS - cast(cast(cast(START_TS as date) as timestamp) + (SLA_TIME - time '00:00:00.000000' hour to second) as timestamp) day(4) to second) + interval '1' minute