I am facing overflow error for below statement please help me in resolving
SEL col * INTERVAL '00:00:01' HOUR TO SECOND from table
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.
The maximum you can cast to an interval is 863999999 seconds using
col * INTERVAL '0000 00:00:01' DAY TO SECOND
Thanks for the reply it is working fine but we wanted output in form of hours to seconds only.
Thanks for the reply tried to cast with less decimal precision but it throws same error please help me with the solution.
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.
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):
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
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.