IDC资讯中心
/*
每个函数都只有一句话!
其实都是从我的另一篇 blog 里抠出来的:
t-sql 生成 两个新的真正的公历年历
http://blog.csdn.net/playyuer/archive/2004/04/07/2860.aspx
t-sql 生成一个简易的 公历年历 t-sql 含日期所在月及年的周次
http://blog.csdn.net/playyuer/archive/2004/04/05/2859.aspx
由于使用了 (@@datefirst + datepart(weekday,@date)) % 7 判断周几
因此与 datefirst 无关,且可适应各种语言版本的 sql server
*/
–周日算作(上一)周的最后一天
create function udf_weekofyear(@date datetime)
–求 @date 所在周是当年的第几周
–周日算作(上一)周的最后一天
–用于按周汇总 group by 时,不要有跨年数据,或者同时 group by year
–group by year(date),month(date), dbo.udf_weekofyear(date),dbo.udf_weekofmonth(date)
returns int
as
begin
return
(select datediff(week
,case when (@@datefirst + datepart(weekday,dateadd(day,0,datediff(day,0,dateadd(year,datediff(year,0,@date),0))))) % 7 = 1
then dateadd(day,-1,dateadd(day,0,datediff(day,0,dateadd(year,datediff(year,0,@date),0))))
else dateadd(day,0,datediff(day,0,dateadd(year,datediff(year,0,@date),0))) –date 所在年的第一天 即: 一月一号
end
,case when (@@datefirst + datepart(weekday,@date)) % 7 = 1
then dateadd(day,-1,@date)
else @date
end
) + 1)
end
go
create function udf_weekofmonth(@date datetime)
–求 @date 所在周是当月的第几周
–周日算作(上一)周的最后一天
–用于按周汇总 group by 时,不要有跨月跨年数据,或者同时 group by year,month
–group by year(date),month(date), dbo.udf_weekofyear(date),dbo.udf_weekofmonth(date)
returns int
as
begin
return
( select datediff(week
,case when (@@datefirst + datepart(weekday,dateadd(month,datediff(month,0,@date),0))) % 7 = 1
then dateadd(month,datediff(month,0,@date),0) – 1
else dateadd(month,datediff(month,0,@date),0)
end
,case when (@@datefirst + datepart(weekday,@date)) % 7 = 1
then @date-1
else @date
end
) + 1 )
end
go
create function udf_weekday(@ int,@date datetime)
returns datetime
as
begin
/*
–周日算作(上一)周的最后一天
当 @ <= 1 代表将 @date 映射到 所在周的星期一
当 @ = 2 代表将 @date 映射到 所在周的星期二
当 @ = 3 代表将 @date 映射到 所在周的星期三
当 @ = 4 代表将 @date 映射到 所在周的星期四
当 @ = 5 代表将 @date 映射到 所在周的星期五
当 @ = 6 代表将 @date 映射到 所在周的星期六
当 @ >= 7 代表将 @date 映射到 所在周的星期日
可用于按周汇总 group by,均支持跨年跨月数据
*/
return
(select –@date,datename(weekday,@date),(@@datefirst + datepart(weekday,@date)) % 7,3 – (@@datefirst + datepart(weekday,@date)) % 7,
dateadd(day
,case when (@@datefirst + datepart(weekday,@date)) % 7 = 0 –周六
then
case when @ between 1 and 6
then @ – 6
else 1
end
when (@@datefirst + datepart(weekday,@date)) % 7 = 1 –周日(七)
then
case when @ between 1 and 6
then @ – 7
else 0
end
when (@@datefirst + datepart(weekday,@date)) % 7 between 2 and 6 –周一至周五
then
case when @ between 1 and 6
then @ + 1 – (@@datefirst + datepart(weekday,@date)) % 7
else 8 – (@@datefirst + datepart(weekday,@date)) % 7
end
end
,@date))
/*
测试:
select date,datename(weekday,date),映射到:,dbo.udf_weekday(2,date),datename(weekday,dbo.udf_weekday(1,date))
from t
order by date
–===============
set datefirst 4
declare @ int,@a int
set @ = 1
select date,datename(weekday,date),(@@datefirst + datepart(weekday,date)) % 7,3 – (@@datefirst + datepart(weekday,date)) % 7,
dateadd(day
,case when (@@datefirst + datepart(weekday,date)) % 7 = 0 –周六
then
case when @ between 2 and 7
then -(7-@)
else @
end
when (@@datefirst + datepart(weekday,date)) % 7 = 1 –周日
then
case when @ between 2 and 7
then -(7-@)-1
else @ – 1
end
when (@@datefirst + datepart(weekday,date)) % 7 between 2 and 6
then
case when @ between 2 and 7
then @ – (@@datefirst + datepart(weekday,date)) % 7
else 8 – (@@datefirst + datepart(weekday,date)) % 7
end
end
,date)
from d
order by date
*/
end
