CODEWITHSUNDEEP
teradata
StrugglingCoder
StrugglingCoder

Reputation: 5011

How to use a subquery inside an INTERVAL function in Teradata?

I want to use Interval function in Teradata much like this :

SELECT DATE '1998-08-31' + INTERVAL '30' DAY;

But I want to make the date range configurable. That is I want it to be read from a table instead of using a hard coded value like '30' in the above example. How can I achieve this ? The table from where the date range will be read from will be storing the range as an integer. So I guess it should well be converted to VARCHAR also. Please clarify. Apparently this did not work

SELECT DATE '1998-08-31' + INTERVAL CAST((SELECT config.ask_gift_dt_diff 
FROM ddcoe_tbls.gift_lnk_config config
WHERE bzd_cnst_lnk_ctg_cd = 'ASK GIFT DATE RANGE') AS VARCHAR(20)) DAY

Upvotes: 0

Views: 504

Answers (1)

dnoeth
dnoeth

Reputation: 60462

No, this will not work, '30' is not a string, it's a part of an interval.

You need to either CAST the number to an INTERVAL DAY or multiply an INTERVAL times that number:

SELECT DATE '1998-08-31' 
 +  CAST((SELECT config.ask_gift_dt_diff 
          FROM ddcoe_tbls.gift_lnk_config config
          WHERE bzd_cnst_lnk_ctg_cd = 'ASK GIFT DATE RANGE') AS INTERVAL DAY)

SELECT DATE '1998-08-31' 
 + (SELECT config.ask_gift_dt_diff * INTERVAL '1' DAY
    FROM ddcoe_tbls.gift_lnk_config config
    WHERE bzd_cnst_lnk_ctg_cd = 'ASK GIFT DATE RANGE')

Upvotes: 2

Related Questions