获取当月的天数列表

完成这个要求之前，可以先参考另外一个函数《获取当月的天数列表》：

然后要知道标题三个节日的常识，母亲节在每年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年所有以上三个节日的日期，帮忙检查一下，是否正确？

获取当月的天数列表

相关推荐