how to use column as unicode literal .. for addin intrvl keyword

General
Enthusiast

how to use column as unicode literal .. for addin intrvl keyword

sel current_DATE + INTRVL '1' day

i want to use '1' as a variable which is a column in my table ..
how do i do it without writing any proceedures or any ither work aroud is appreciated ..
1 REPLY
Junior Contributor

Re: how to use column as unicode literal .. for addin intrvl keyword

What datatype is this column?

Numeric?
For days you don't need the interval syntax and you don't want it because it's limited to 9999 days. Simply use the old Teradata style:
select current_date + mycol

Char?
You can use the same, too:
select current_date + mycol

Or add an explicit typecast:
select current_date + cast(mycol as int)

If you want to add months, better use ADD_MONTHS instead of INTERVAL MONTH, because the latter will fail e.g. for Jan. 31 + 1 month.

For seconds etc. you might use
select current_timestamp + (interval '0001 00:00:01' day to second * mycol)

This enables more than 9999 seconds/minutes/hours up to approx. 28 years.

Dieter