Week Function

 今天是母亲节，它是在每一年的五月份的第二个星期天，而父亲节，是在每一个的六月份的第三个星期天。

把星期天设置为每周的开始,将一周的第一天设置为从 1 到 7 的一个数字。

set datefirst 7;  

参考msdn:然后，我们需要写一个自定义函，获取一年之中所有周别数据：

《week function》

 或者拷贝下面代码即可(稍有修改)：

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

 
把这个要求，写成一个自定义函数，方便用在程序应用即可。

 

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

 

下面代码年份，看看得到的日期是否正确：