All Forums Database
Partha Sarathy 1 post Joined 01/16
26 Jan 2016
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:
Adeel Chaudhry 773 posts Joined 04/08
26 Jan 2016

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;

 

-- If you are stuck at something .... consider it an opportunity to think anew.

Fred 1096 posts Joined 08/04
27 Jan 2016

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

dins2k2 51 posts Joined 05/13
02 Feb 2016

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

You must sign in to leave a comment.