获取某一年的母亲节或父亲节日期

 今天是母亲节，它是在每一年的五月份的第二个星期天，而父亲节，是在每一个的六月份的第三个星期天。

把星期天设置为每周的开始,将一周的第一天设置为从 1 到 7 的一个数字。

SET DATEFIRST 7;  

参考MSDN:https://docs.microsoft.com/zh-cn/sql/t-sql/statements/set-datefirst-transact-sql?view=sql-server-2017

然后，我们需要写一个自定义函，获取一年之中所有周别数据：

《Week Function》https://www.cnblogs.com/insus/archive/2009/12/13/1622988.html

 或者拷贝下面代码即可(稍有修改)：

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[svf_Week]
(
    @StartYear INT,
    @EndYear INT
)
RETURNS @Week TABLE([ID] INT IDENTITY(1,1) PRIMARY KEY,[Year] [INT] NULL,[Week] [INT] NULL,[StartDate] [DATETIME] NULL,[EndDate] [DATETIME] NULL)
AS
BEGIN
    DECLARE @StartDateOfYear DATETIME 
    DECLARE @LastDateOfYear DATETIME 
    DECLARE @WeekStartDate DATETIME 
    DECLARE @WeekEndDate DATETIME 
    DECLARE @Weeks INT 

    WHILE @StartYear <= @EndYear
    BEGIN
        SET @StartDateOfYear = CAST((CAST(@StartYear AS VARCHAR(4)) + '-01-01') AS DATETIME)
        SET @LastDateOfYear= CAST((CAST(@StartYear AS VARCHAR(4))+ '-12-31') AS DATETIME)
        SET @Weeks = 1         
        
        DECLARE @WeekStartDateOfYear DATETIME
        IF DATEPART(DW,@StartDateOfYear) > 4
            SET @WeekStartDateOfYear = DATEADD(DAY,(8 - DATEPART(DW,@StartDateOfYear)) ,@StartDateOfYear)
        ELSE 
            SET @WeekStartDateOfYear = DATEADD(DAY,(-(DATEPART(DW,@StartDateOfYear)-1)),@StartDateOfYear)

        SET @WeekStartDate = @WeekStartDateOfYear
        SET @WeekEndDate = DATEADD(DAY,6,@WeekStartDate)
        WHILE DATEDIFF(DAY,@WeekStartDate,@LastDateOfYear) >= 4
        BEGIN
            INSERT INTO @Week([Year],[Week],[StartDate],[EndDate]) VALUES (@StartYear,@Weeks,@WeekStartDate,@WeekEndDate)
            SET @Weeks = @Weeks + 1
            SET @WeekStartDate = @WeekStartDate + 7
            SET @WeekEndDate = @WeekEndDate + 7
        END
        SET @StartYear = @StartYear + 1
    END  
        RETURN
END
GO

 
把这个要求，写成一个自定义函数，方便用在程序应用即可。

 

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      Insus.NET
-- Create date: 2019-05-12
-- Update date: 2019-05-12
-- Description: 获取母亲节或父亲节日期
-- =============================================
CREATE FUNCTION [dbo].[svf_Parents_Festival]
(
    @StartYear INT,
    @EndYear INT
)
RETURNS @tempTable TABLE([ID] INT IDENTITY(1,1) PRIMARY KEY,[Year] [INT] NOT NULL,[Mother's Day] [DATETIME] NULL,[Father's Day] [DATETIME] NULL)
AS
BEGIN       
    DECLARE @Weeks AS TABLE([Year] INT,[StartDateOfWeek] DATETIME)
    INSERT INTO @Weeks ([Year],[StartDateOfWeek]) SELECT [Year],[StartDate] FROM [dbo].[svf_Week] (@StartYear,@EndYear)
    
    WHILE @StartYear <= @EndYear
    BEGIN
        INSERT INTO @tempTable ([Year]) VALUES(@StartYear)      

        UPDATE @tempTable SET [Mother's Day] = (
            SELECT [StartDateOfWeek] FROM (
                SELECT ROW_NUMBER() OVER (ORDER BY [StartDateOfWeek] ASC) AS [RowNumber], [StartDateOfWeek] FROM @Weeks 
                WHERE [Year] = @StartYear AND MONTH([StartDateOfWeek]) = 5) AS m 
            WHERE [RowNumber] = 2)
        WHERE [Year] = @StartYear

        UPDATE @tempTable SET [Father's Day] = (
            SELECT [StartDateOfWeek] FROM (
                SELECT ROW_NUMBER() OVER (ORDER BY [StartDateOfWeek] ASC) AS [RowNumber], [StartDateOfWeek] FROM @Weeks 
                WHERE [Year] = @StartYear AND MONTH([StartDateOfWeek]) = 6) AS f 
            WHERE [RowNumber] = 3)
        WHERE [Year] = @StartYear

        SET @StartYear = @StartYear + 1
    END  

    RETURN
END

 

下面代码年份，看看得到的日期是否正确：