need teradata logic

Database

need teradata logic

Hi,

I have a 'Dob_Date' column with date format 'dd-mm-yyyy'. I need to extract the year from it and

need to add year+1(i.e Adding one year) and insert that into another column named 'Modified1_date',

need to add year+2(i.e Adding two year) and insert that into another column named 'Modified2_date',

need to add year+3(i.e Adding three year) and insert that into another column named 'Modified3_date',

need to add year+4(i.e Adding four year) and insert that into another column named 'Modified4_date'.


Need to handle the above logic in single query, Can anyone help me on this.

Tags (1)
3 REPLIES
Teradata Employee

Re: need teradata logic

You can use following:

CREATE VOLATILE TABLE temp
(
col1 DATE
,col2 DATE
,col3 DATE
,col4 DATE
) ON COMMIT PRESERVE ROWS;

INSERT temp(col1) VALUES(CURRENT_DATE);

SELECT * FROM temp;

UPDATE temp
SET
col2 = col1 + INTERVAL '1' YEAR
,col3 = col1 + INTERVAL '2' YEAR
,col4 = col1 + INTERVAL '3' YEAR
;

SELECT * FROM temp;
Teradata Employee

Re: need teradata logic

INTERVAL YEAR will fail for February 29. Better to use ADD_MONTHS(col1,12) and so forth.

Enthusiast

Re: need teradata logic

Partha,

Fred was right. INTERVAL YEAR will fail for February 29. Check the below link.

https://forums.teradata.com/forum/database/how-to-write-current-date-1-year-in-teradata

Thanks,

Dinesh