欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

获取当月的天数列表

程序员文章站 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年所有以上三个节日的日期,帮忙检查一下,是否正确?