Find last sunday

Question

How will you find last sunday of a month in sql 2000?

Answer 1

An alternative approach, borrowed from data warehousing practice. Create a date-dimension table and pre-load it for 10 years, or so.

TABLE dimDate (DateKey, FullDate, Day, Month, Year, DayOfWeek, 
               DayInEpoch, MonthName, LastDayInMonthIndicator, many more..)

The easiest way to fill-in the dimDate is to spend an afternoon with Excel and then import to DB from there. A half-decent dimDate table has 50+ columns -- anything you ever wanted to know about a date.

With this in place, the question becomes something like:

SELECT max(FullDate)
FROM dimDate
WHERE DayOfWeek = 'Sunday'
      AND Month = 11
      AND Year = 2009;

Essentially, all date related queries become simpler.

Answer 2

select dateadd(day,1-datepart(dw, getdate()), getdate())

Answer 3

select next_day(last_day(sysdate)-7, 'Sunday') from dual