今天是母亲节，它是在每一年的五月份的第二个星期天，而父亲节，是在每一个的六月份的第三个星期天。
把星期天设置为每周的开始,将一周的第一天设置为从 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
下面代码年份，看看得到的日期是否正确：