T-SQL: 三个通用的与日期相关的,辅助按周(星期日是周的最后一天)汇总的自定义函数

王朝mssql·作者佚名  2006-01-09
窄屏简体版  字體: |||超大  

/*

每个函数都只有一句话!

其实都是从我的另一篇 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

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
 
 
© 2005- 王朝網路 版權所有 導航