Invalid literal interval Issue

General
Enthusiast

Invalid literal interval Issue

Hi Team,

 

My source col is decimal(19,4) and Target is Interval field we had interval overflow issue while performing col*HOUR TO SECOND so business said to use day to second like if col*INTERVAL '0000 00:00:00.000001' DAY TO SECOND > 9999 23:59:59.000000 then 9999 23:59:59.000000 else col value so i tried to use below sql it throws 3706:invalid interval error please help me with resolution

 

select
CASE
WHEN col * INTERVAL '0000 00:00:00.000001' DAY TO SECOND > interval '9 23:59:59.000000' day to second THEN interval

'9 23:59:59.000000' day to second
ELSE col * INTERVAL '00000 00:00:00.000001' DAY TO SECOND
END AS col from table

 

Note: col * INTERVAL '0000 00:00:00.000001' DAY TO SECOND format=0 00:00:00.000000

 

Thanks,

Pavan

 

2 REPLIES 2
Teradata Employee

Re: Invalid literal interval Issue

The ELSE clause has 5 zeros for the day portion but only 4 digits are allowed.

 

Is the "whole number" portion of the DECIMAL(19,4) value really microseconds? That's what this calculation implies.

Highlighted
Enthusiast

Re: Invalid literal interval Issue

Hi Fred,

Yes the whole number has microseconds.

Thanks,
Pavan