Find last sunday

Question

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

Answer 1

Holy cow, this is ugly, but here goes:

DECLARE @dtDate DATETIME
SET @dtDate = '2009-11-05'

SELECT DATEADD(dd, -1*(DATEPART(dw, DateAdd(day, -1, DateAdd(month, DateDiff(month, 0, @dtDate)+1, 0)))-1),
      DateAdd(day, -1, DateAdd(month, DateDiff(month, 0, @dtDate)+1, 0)))

Answer 2

SELECT
 DATEADD(day,DATEDIFF(day,'19000107',DATEADD(month,DATEDIFF(MONTH,0,GETDATE() /*YourValuehere*/),30))/7*7,'19000107')

Edit: A correct, final, working answer from my colleague.

Answer 3

First built a tally table. http://www.sqlservercentral.com/articles/T-SQL/62867/ then get what you want..

http://www.sqlservercentral.com/Forums/Topic515226-1291-1.aspx

DECLARE @DateStart DATETIME,
        @DateEnd   DATETIME

 SELECT @DateStart = '20080131',
        @DateEnd   = '20101201'

 SELECT DATEADD(wk,DATEDIFF(wk,6,DATEADD(mm,DATEDIFF(mm,-1,DATEADD(mm,t.N-1,@DateStart)),-1)),6)
   FROM dbo.Tally t
  WHERE t.N <= DATEDIFF(mm,@DateStart,@DateEnd)

Answer 4

DECLARE @LastDateOfMonth smalldatetime
SELECT @LastDateOfMonth = DATEADD(month, DATEDIFF(month, -1, GETDATE()), 0) -1
Select DATEADD(dd,-( CASE WHEN DATEPART(weekday,@LastDateOfMonth) = 1 THEN 0 ELSE DATEPART(weekday,@LastDateOfMonth) - 1 END ),@LastDateOfMonth)

Answer 5

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 6

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