获取当月的天数列表
程序员文章站
2022-03-20 20:10:45
完成这个要求之前,可以先参考另外一个函数《获取当月的天数列表》https://www.cnblogs.com/insus/p/10837900.html: 然后要知道标题三个节日的常识,母亲节在每年5月份的第二个星期天,父亲节在每年6月份的第三个星期天,而感恩节是在每年的11月份第四个星期的星期四。 ......
完成这个要求之前,可以先参考另外一个函数《获取当月的天数列表》:
然后要知道标题三个节日的常识,母亲节在每年5月份的第二个星期天,父亲节在每年6月份的第三个星期天,而感恩节是在每年的11月份第四个星期的星期四。
知道这些常识就好办了。
写一个sql的自定义函数:
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_festivals] ( @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,[thanksgiving day] datetime) as begin while @startyear <= @endyear begin insert into @temptable ([year]) values(@startyear) update @temptable set [mother's day] = ( select [date] from ( select row_number() over (order by [date] asc) as [rownumber], [date] from [dbo].[tvf_daysofmonth](cast(@startyear as nvarchar(4)) + '-05-01') where datename(dw,[date]) = 'sunday') as md where [rownumber] = 2) where [year] = @startyear update @temptable set [father's day] = ( select [date] from ( select row_number() over (order by [date] asc) as [rownumber], [date] from [dbo].[tvf_daysofmonth](cast(@startyear as nvarchar(4)) + '-06-01') where datename(dw,[date]) = 'sunday') as fd where [rownumber] = 3) where [year] = @startyear update @temptable set [thanksgiving day] = ( select [date] from ( select row_number() over (order by [date] asc) as [rownumber], [date] from [dbo].[tvf_daysofmonth](cast(@startyear as nvarchar(4)) + '-11-01') where datename(dw,[date]) = 'thursday') as td where [rownumber] = 4) where [year] = @startyear set @startyear = @startyear + 1 end return end go
下面是列出2019至2025年所有以上三个节日的日期,帮忙检查一下,是否正确?