Invalid literal interval Issue

General
Highlighted
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.

Enthusiast

Re: Invalid literal interval Issue

Hi Fred,

Yes the whole number has microseconds.

Thanks,
Pavan