How to write (CURRENT DATE - 1 YEAR) in teradata?

Database
Enthusiast

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

8 REPLIES
Enthusiast

Re: How to write (CURRENT DATE - 1 YEAR) in teradata?

Using Interval we can subtract years from current date

SELECT CURRENT_DATE- INTERVAL '1' YEAR

Junior Supporter

Re: How to write (CURRENT DATE - 1 YEAR) in teradata?

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.

Enthusiast

Re: How to write (CURRENT DATE - 1 YEAR) in teradata?

Hi Carlosal

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

Thanks

Re: How to write (CURRENT DATE - 1 YEAR) in teradata?

This was super helpful. thanks

Enthusiast

Re: How to write (CURRENT DATE - 1 YEAR) in teradata?

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

Junior Contributor

Re: How to write (CURRENT DATE - 1 YEAR) in teradata?

Hi Ghalia,

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

Re: How to write (CURRENT DATE - 1 YEAR) in teradata?

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)

Junior Contributor

Re: How to write (CURRENT DATE - 1 YEAR) in teradata?

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