内容简介:今天是母亲节,它是在每一年的五月份的第二个星期天,而父亲节,是在每一个的六月份的第三个星期天。把星期天设置为每周的开始,《
今天是母亲节,它是在每一年的五月份的第二个星期天,而父亲节,是在每一个的六月份的第三个星期天。
把星期天设置为每周的开始, 将一周的第一天设置为从 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 Source Code
把这个要求,写成一个自定义函数,方便用在程序应用即可。
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 Source Code
下面代码年份,看看得到的日期是否正确:
以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持 码农网
猜你喜欢:- 获知某一年的母亲节,父亲节和感恩节日期
- ADO.NET获取数据(DataSet)同时获取表的架构实例
- 根据 PID 获取 K8S Pod名称 - 反之 POD名称 获取 PID
- .NET/C# 如何获取当前进程的 CPU 和内存占用?如何获取全局 CPU 和内存占用?
- phpinfo获取敏感内容
- 低开销获取时间戳
本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们。
机器学习实践指南
麦好 / 机械工业出版社 / 2014-4-1 / 69.00
《机器学习实践指南:案例应用解析》是机器学习及数据分析领域不可多得的一本著作,也是为数不多的既有大量实践应用案例又包含算法理论剖析的著作,作者针对机器学习算法既抽象复杂又涉及多门数学学科的特点,力求理论联系实际,始终以算法应用为主线,由浅入深以全新的角度诠释机器学习。 全书分为准备篇、基础篇、统计分析实战篇和机器学习实战篇。准备篇介绍了机器学习的发展及应用前景以及常用科学计算平台,主要包括统......一起来看看 《机器学习实践指南》 这本书的介绍吧!
正则表达式在线测试
正则表达式在线测试
RGB CMYK 转换工具
RGB CMYK 互转工具