查找上周日

Question

如何在 sql 2000 中找到一个月的最后一个星期日？

Answer 1

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

编辑：我同事的正确、最终、有效的答案。

Answer 2

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

Answer 3

另一种方法借鉴了数据仓库实践。创建一个日期维度表并预加载 10 年左右。

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

填写dimDate的最简单方法是花一个下午的时间使用Excel，然后从那里导入到数据库。一个还算不错的 dimDate 表有 50 多列——任何你想知道的关于日期的信息。

有了这个，问题就变成了这样：

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

本质上，所有与日期相关的查询都变得更简单。

Answer 4

SQL 中的下周日，无论哪一天是一周的第一天：在 2010 年 12 月 22 日返回 2011-01-02 23:59:59.000：

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

Answer 5

我发现其中一些解决方案很难理解，因此这是我的版本，其中包含变量来解释这些步骤。

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

Answer 6

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 7

天哪，这很丑陋，但这里是：

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 8

首先建了一个统计表。
http://www.sqlservercentral.com/articles/T-SQL/62867/< /a>
然后得到你想要的..

http://www.sqlservercentral.com/Forums /主题515226-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 9

这是正确的方法，考虑@@DATEFIRST

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fu_dtLastSundayInMonth]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
    EXECUTE(N'CREATE FUNCTION [dbo].[fu_dtLastSundayInMonth]() RETURNS int BEGIN RETURN 0 END ')
END 
GO 



/*
SET DATEFIRST 3; -- Monday

WITH CTE AS (

    SELECT 1 AS i, CAST('20190101' AS datetime) AS mydate  
    UNION ALL 

    SELECT i+1 AS i, DATEADD(month, 1, CTE.mydate) AS mydate 
    FROM CTE WHERE i < 100 
)

SELECT -666 AS i, dbo.fu_dtLastSundayInMonth('17530101') AS lastSundayInMonth, dbo.fu_dtLastSundayInMonth('17530101') AS Control 

UNION ALL 

SELECT -666 AS i, dbo.fu_dtLastSundayInMonth('99991231') AS lastSundayInMonth, dbo.fu_dtLastSundayInMonth('99991231') AS Control 

UNION ALL 

SELECT 
     mydate 
    ,dbo.fu_dtLastSundayInMonth(mydate) AS lastSundayInMonth 
    ,dbo.fu_dtLastSundayInMonth(mydate) AS lastSundayInMonth 
    ,DATEADD(day,DATEDIFF(day,'19000107', DATEADD(MONTH, DATEDIFF(MONTH, 0, mydate, 30))/7*7,'19000107') AS Control 
FROM CTE 

*/


-- =====================================================================
-- Description:   Return date of last sunday in month
--                of the same year and month as @in_DateTime
-- =====================================================================
ALTER FUNCTION [dbo].[fu_dtLastSundayInMonth](@in_DateTime datetime )
RETURNS DateTime
AS
BEGIN
    -- Abrunden des Eingabedatums auf 00:00:00 Uhr
    DECLARE @dtReturnValue AS DateTime  
    -- 26.12.9999   SO
    IF @in_DateTime >= CAST('99991201' AS datetime) 
        RETURN CAST('99991226' AS datetime); 

    -- @dtReturnValue is now last day of month 
    SET @dtReturnValue = DATEADD 
        (
             DAY 
            ,-1
            ,DATEADD
            (
                 MONTH
                ,1
                ,CAST(CAST(YEAR(@in_DateTime) AS varchar(4)) + RIGHT('00' + CAST(MONTH(@in_DateTime) AS varchar(2)), 2) + '01' AS datetime) 
            )
        )
    ;

    -- SET DATEFIRST 1 -- Monday - Super easy ! 
    -- SET DATEFIRST != 1 - PHUK THIS ! 
    SET @dtReturnValue = DATEADD
                        (
                            day
                            ,
                             -
                             (

                                (
                                    -- DATEPART(WEEKDAY, @lastDayofMonth) -- with SET DATEFIRST 1 
                                    DATEPART(WEEKDAY, @dtReturnValue) + @@DATEFIRST - 2 % 7 + 1 
                                )
                                %7
                            )
                            , @dtReturnValue
    );

    RETURN @dtReturnValue; 
END


GO

Answer 10

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