獲取某一年的母親節或父親節日期

 今天是母親節，它是在每一年的五月份的第二個星期天，而父親節，是在每一個的六月份的第三個星期天。

把星期天設置為每周的開始,將一周的第一天設置為從 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

下麵代碼年份，看看得到的日期是否正確：