[MSSQL]根据年得到所有星期日的日期

最新推荐文章于 2023-05-06 13:17:58 发布

厦门德仔

最新推荐文章于 2023-05-06 13:17:58 发布

阅读量1.1k

点赞数

分类专栏： MSSQL 文章标签： join function table 测试

MSSQL 专栏收录该内容

293 篇文章 11 订阅 ¥99.90 ¥299.90

订阅专栏

超级会员免费看

这篇博客介绍了一个SQL函数的创建，该函数用于获取指定年份的所有星期日日期。通过一系列数学运算和日期函数，实现了从1月1日开始的365天中找出每个星期一，并将结果存储在表格中。提供的测试示例展示了如何调用此函数并展示2011年的所有星期日日期。

摘要由CSDN通过智能技术生成

go
--创建函数
create function GetWeekDays(@year int)
returns @t table (星期天varchar(20))
as
begin
insert @t
select substring(convert(varchar,dateadd(day,x,col),120),1,10) from 
( select cast(cast(@year as varchar(4))+'-1-1' as datetime) as col )a cross join 
( select top 365 b8.i+b7.i + b6.i + b5.i + b4.i +b3.i +b2.i + b1.i + b0.i x 
from(select 0 i union all select 1) b0 
cross join(select 0 i union all select 2) b1 
cross join(select 0 i union all select 4) b2 
cross join(select 0 i union all select 8) b3 
cross join(select 0 i union all select 16) b4 
cross join(select 0 i union all select 32) b5 
cross join(select 0 i union all select 64) b6 
cross join(select 0 i union all select 128) b7 
cross join(select 0 i union all select 256) b8 
order by 1 )b where datepart(dw,dateadd(day,x,col))=1
return
end

--测试示例
select * from dbo.GetWeekDays(2011)
--运行结果
/*
星期天
--------------------
2011-01-02
2011-01-09
2011-01-16
2011-01-23
2011-01-30
2011-02-06
2011-02-13
2011-02-20
2011-02-27
2011-03-06
2011-03-13
2011-03-20
2011-03-27
2011-04-03
2011-04-10
2011-04-17
2011-04-24
2011-05-01
2011-05-08
2011-05-15
2011-05-22
2011-05-29
2011-06-05
2011-06-12
2011-06-19
2011-06-26
2011-07-03
2011-07-10
2011-07-17
2011-07-24
2011-07-31
2011-08-07
2011-08-14
2011-08-21
2011-08-28
2011-09-04
2011-09-11
2011-09-18
2011-09-25
2011-10-02
2011-10-09
2011-10-16
2011-10-23
2011-10-30
2011-11-06
2011-11-13
2011-11-20
2011-11-27
2011-12-04
2011-12-11
2011-12-18
2011-12-25
(52 row(s) affected)
*/