Teradata Error 7453 Interval field overflow issue

Database
Enthusiast

Teradata Error 7453 Interval field overflow issue

Hi All,

 

I am facing overflow error for below statement please help me in resolving

 

SEL col * INTERVAL '00:00:01' HOUR TO SECOND from table

 

col datatype=DECIMAL(19,4)

 

Thanks,

Pavan

8 REPLIES 8
Teradata Employee

Re: Teradata Error 7453 Interval field overflow issue

Decimal(19,4) is big enough to count the seconds in nearly 317 million years, which we cannot compute dates for. Try casting col to a smaller scale, like Dec(16,4) or smaller.

Highlighted
Ambassador

Re: Teradata Error 7453 Interval field overflow issue

The maximum you can cast to an interval is 863999999 seconds using

col * INTERVAL '0000 00:00:01' DAY TO SECOND
Enthusiast

Re: Teradata Error 7453 Interval field overflow issue

Hi,

 

Thanks for the reply it is working fine but we wanted output in form of  hours to seconds only.

 

Thanks,

Pavan

Enthusiast

Re: Teradata Error 7453 Interval field overflow issue

Hi,

 

Thanks for the reply tried to cast with less decimal precision but it throws same error please help me with the solution.

 

Thanks,

Pavan

Teradata Employee

Re: Teradata Error 7453 Interval field overflow issue

You can CAST a date/time to larger precision but not to smaller precision.

Default precision for SECOND is 6 but you can explicitly give precision for the constant: INTERVAL '00:00:01' HOUR TO SECOND(0)

 

Another option would be to CAST the result to VARCHAR, use SUBSTRING to drop the extra part, then CAST back to the lower precision type.

Ambassador

Re: Teradata Error 7453 Interval field overflow issue

If you insist on hours to second you're limited to < 10000 hours:

 

col * INTERVAL '0000:00:01' HOUR TO SECOND

Or you omit intervals and calculate the number of seconds using a UDF (or similar calculation):

 

https://community.teradata.com/t5/Analytics/Failure-7453-Interval-field-overflow/m-p/2074/highlight/...

 

Enthusiast

Re: Teradata Error 7453 Interval field overflow issue

Hi Fred,

 

I am facing error with below SQL please help me with the correct SQL

 

SEL Col from * INTERVAL '00:00:01' HOUR TO SECOND(0) from table

 

Col=decimal(19,4)

 

Thanks,

Pavan

Teradata Employee

Re: Teradata Error 7453 Interval field overflow issue

See dnoeth's reply.

My prior suggestion was incorrect. The precision of the interval literal is derived from the precision of the value in quotes and cannot be explicitly specified.

If you are willing to use INTERVAL DAY TO SECOND you can get a range up to 9999 days 23 hours 59 minutes 59 seconds.