T-SQL: 三个通用的与日期相关的,辅助按周(星期日是周的最后一天)汇总的自定义函数
/*
每个函数都只有一句话!
其实都是从我的另一篇 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_WeekOfQuarter(@date datetime)
returns int
as
begin
return
( select datediff(week
,case when (@@datefirst + datepart(weekday,dateadd(Quarter,datediff(Quarter,0,@date),0))) % 7 = 1
then dateadd(Quarter,datediff(Quarter,0,@date),0) - 1
else dateadd(Quarter,datediff(Quarter,0,@date),0)
end
,case when (@@datefirst + datepart(weekday,@date)) % 7 = 1
then @date - 1
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
create function udf_WeekDiff(@BeginDate datetime,@EndDate datetime)
returns integer
begin
return (
select datediff(week,@BeginDate,@EndDate) -- + 1
+ case when (@@datefirst + datepart(weekday,@BeginDate)) % 7 = 1 then 1 else 0 end
- case when (@@datefirst + datepart(weekday,@EndDate)) % 7 = 1 then 1 else 0 end
)
end
go
--测试:
declare @b datetime,@e datetime
set @b = '2004-12-12'
set @e = '2004-12-13'
select dbo.udf_WeekDiff(@b,@e) + 1 as 跨周数
,dbo.udf_WeekDiff(@b,@e) as 间隔周数
,datediff(week,@b,@e) as [datediff(week,startdate,enddate)]
,@b as [日期]
,dbo.udf_WeekDay(1,@b) as 日期所在周的星期一
