Minute(4) issue in teradata

Database
Enthusiast

Minute(4) issue in teradata

I have values below for which diff field is giving error like 
"[6760] invalid timestamp" in teradata
 
Might be it is not doing calculation anymore after exceeding minute(4) value 

 

Could you please help here.

 

Code:
END_TS = 2/2/2018 08:50:49.000000
START_TS = 1/5/2018 17:30:02.000000
SLA_TIME = 23:59:59.000000

 

Code:
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
Tags (2)
3 REPLIES
Teradata Employee

Re: Minute(4) issue in teradata

Could you confirm the datatypes in your table ?

timestamp(6) for START_TS and END_TS

time(6) from SLA_TIME

Tags (1)
Enthusiast

Re: Minute(4) issue in teradata

Absolutely Right  @Waldar

 

Teradata Employee

Re: Minute(4) issue in teradata

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