Find last sunday

问题:

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

回答1:

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.

回答2:

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

回答3:

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.

回答4:

Next sunday in SQL, regardless which day is first day of week: returns 2011-01-02 23:59:59.000 on 22-dec-2010:

select DateADD(ss, -1, DATEADD(week, DATEDIFF(week, 0, getdate()), 14))

回答5:

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)

回答6:

I find some of these solutions hard to understand so here's my version with variables to explain the steps.

ALTER FUNCTION dbo.fn_LastSundayInMonth
(
  @StartDate DATETIME
 ,@RequiredDayOfWeek INT    /* 1= Sunday */
)
RETURNS DATETIME
AS
/*
A detailed step by step way to get the answer...

SELECT dbo.fn_LastSundayInMonth(getdate()-31,1)
SELECT dbo.fn_LastSundayInMonth(getdate()-31,2)
SELECT dbo.fn_LastSundayInMonth(getdate()-31,3)
SELECT dbo.fn_LastSundayInMonth(getdate()-31,4)
SELECT dbo.fn_LastSundayInMonth(getdate()-31,5)
SELECT dbo.fn_LastSundayInMonth(getdate()-31,6)
SELECT dbo.fn_LastSundayInMonth(getdate()-31,7)
*/
BEGIN
    DECLARE @MonthsSince1900 INTEGER
    DECLARE @NextMonth INTEGER
    DECLARE @DaysToSubtract INTEGER
    DECLARE @FirstDayOfNextMonth DATETIME
    DECLARE @LastDayOfMonthDayOfWeek INTEGER
    DECLARE @LastDayOfMonth DATETIME
    DECLARE @ReturnValue DATETIME

    SET @MonthsSince1900=DateDiff(month, 0, @StartDate)
    SET @NextMonth=@MonthsSince1900+1
    SET @FirstDayOfNextMonth = DateAdd(month,@NextMonth, 0)
    SET @LastDayOfMonth = DateAdd(day, -1, @FirstDayOfNextMonth)

    SET @ReturnValue = @LastDayOfMonth

    WHILE DATEPART(dw, @ReturnValue) <> @RequiredDayOfWeek
        BEGIN
            SET @ReturnValue = DATEADD(DAY,-1, @ReturnValue)
        END

    RETURN @ReturnValue
END

回答7:

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)))

回答8:

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)

回答9:

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