获取某一年的母亲节或父亲节日期
今天是母亲节,它是在每一年的五月份的第二个星期天,而父亲节,是在每一个的六月份的第三个星期天。
把星期天设置为每周的开始,将一周的第一天设置为从 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
或者拷贝下面代码即可(稍有修改):
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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
把这个要求,写成一个自定义函数,方便用在程序应用即可。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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
下面代码年份,看看得到的日期是否正确: