All Forums Database
teradata006 8 posts Joined 03/12
25 Oct 2012
How to write (CURRENT DATE - 1 YEAR) in teradata?

Can anyone help here how to write/convert  this (CURRENT DATE - 1 YEAR) in teradata.
 
Thanks
 

Mathuram 21 posts Joined 09/12
25 Oct 2012

Using Interval we can subtract years from current date
SELECT CURRENT_DATE- INTERVAL '1' YEAR

CarlosAL 512 posts Joined 04/08
26 Oct 2012

Hi.
The solution provided is wrong (or at least not completely right).
You should use ADD_MONTH() instead, because:
SELECT DATE '2012-02-29' -  INTERVAL '1' YEAR;

 *** Failure 2665 Invalid date.
                Statement# 1, Info =0
 *** Total elapsed time was 1 second.
but
SELECT ADD_MONTHS(DATE '2012-02-29', - 12);

 *** Query completed. One row found. One column returned.
 *** Total elapsed time was 1 second.

ADD_MONTHS(2012-02-29, -12)
---------------------------
                 2011-02-28
HTH.
Cheers.
Carlos.

Mathuram 21 posts Joined 09/12
26 Oct 2012

Hi Carlosal
I wil accept you, in somecases using INTERVAL provides only error, so using ADD_MONTHS is best at all times.
Thanks

brnkptr 2 posts Joined 08/14
20 Aug 2014

This was super helpful. thanks

Ghalia 11 posts Joined 12/15
28 Dec 2015

Hello,
 
In Teradata 14.10 , it works great just like this : SELECT CURRENT_DATE- INTERVAL '1' YEAR !
Is it still bad to use it like this ?
 
Regards,
 
Ghalia

dnoeth 4628 posts Joined 11/04
29 Dec 2015

Hi Ghalia,
wait for '2016-02-29' and you'll see why ADD_MONTHS(CURRENT_DATE, 12) is much better.

Dieter

sriteradata88 3 posts Joined 02/16
07 Feb 2016

Hi,
 
i'm trying to pull data between date -1 and last 8months
so will this work better ADD_MONTHS(CURRENT_DATE-1, 8)
 
or ( between current_date -241 and current_date -1)
 
 
 

dnoeth 4628 posts Joined 11/04
08 Feb 2016

Eight months are not always 241 days, so the calculation should be based on your logic.

Dieter

You must sign in to leave a comment.