Access开发培训
网站公告
·Access专家课堂QQ群号:151711184    ·Access快速开发平台下载地址及教程    ·欢迎加入Access专家课堂微信群!    ·如何快速搜索本站文章|示例|资料    
您的位置: 首页 > 技术文章 > ADP及SQL SERVER

SQLServer根据年得到所有星期日的日期

时 间:2015-04-28 08:58:14
作 者:金宇   ID:43  城市:江阴
摘 要:SQLServer根据年得到所有星期日的日期
正 文:

--创建函数
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)

*/


转载来源:http://blog.csdn.net/maco_wang/article/details/6272748



Access软件网官方交流QQ群 (群号:54525238)       Access源码网店

常见问答:

技术分类:

相关资源:

专栏作家

关于我们 | 服务条款 | 在线投稿 | 友情链接 | 网站统计 | 网站帮助