Teradata SQL Same Day Prior Year in same Week

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.

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.