Date:         Tue, 26 Jul 2011 22:51:18 +0000
Reply-To:     "DUELL, BOB (ATTCINW)" <bd9439@ATT.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "DUELL, BOB (ATTCINW)" <bd9439@ATT.COM>
Subject:      Re: Teradata Dates
Comments: To: Dave Smith <daveyboysmith_123@YAHOO.COM>
In-Reply-To:  <201107262149.p6QJ46uC019235@waikiki.cc.uga.edu>
Content-Type: text/plain; charset="us-ascii"

You almost had it; use the ADD_MONTHS function and go backward by 24 months:

date_column between add_months(current_date - extract(day from current_date),-24)
                and (current_date - extract(day from current_date)) ;

As originally written, your query would not have worked because the word DATE is a reserved keyword and means the current system date (exactly the same as "current_date").  I'm assuming that was just a flaw in your example.

And yes, this is a Teradata-specific question and doesn't have anything to do with SAS, but it's a slow day.

Good luck!

Bob

-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Dave Smith
Sent: Tuesday, July 26, 2011 2:50 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Teradata Dates

Hello All,

I am stuck in a Teradata/sql question, not really sure if its the right
place to ask:

I want to write a query to get results for only last two years in teradata,
the syntax I use has issues with leap years etc, can anyone please let me
know if there is a better way:

thanks

I go back two years from the last day of the previous month:

DATE between   ((current_date - EXTRACT(DAY FROM current_date)+1)-365.25*2)
and  (current_date - EXTRACT(DAY FROM current_date)) ;