Need help figuring out how to determine if the date is the same ‘day’ as today in teradata. IE, today 12/1/15 Tuesday
, same day last year was actually 12/2/2014 Tuesday
.
I tried using current_date - INTERVAL'1'Year
but it returns 12/1/2014
.
Advertisement
Answer
You can do this with a bit of math if you can convert your current date’s “Day of the week” to a number, and the previous year’s “Day of the week” to a number.
In order to do this in Teradata your best bet is to utilize the sys_calendar.calendar
table. Specifically the day_of_week
column. Although there are other ways to do it.
Furthermore, instead of using CURRENT_DATE - INTERVAL '1' YEAR
, it’s a good idea to use ADD_MONTHS(CURRENT_DATE, -12)
since INTERVAL
arithmetic will fail on 2012-02-29
and other Feb 29th leap year dates.
So, putting it together you get what you need with:
SELECT ADD_MONTHS(CURRENT_DATE, -12) + ( (SELECT day_of_week FROM sys_calendar.calendar WHERE calendar_date = CURRENT_DATE) - (SELECT day_of_week FROM sys_calendar.calendar WHERE calendar_date = ADD_MONTHS(CURRENT_DATE, -12)) )
This is basically saying: Take the current dates day of week number (3) and subtract from it last years day of week number (2) to get 1. Add that to last year’s date and you’ll have the same day of the week as current date.
I tested this for all dates between 01/01/2010
and CURRENT_DATE
and it worked as expected.