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

<script type="text/javascript"> </script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>

<script type="text/javascript"> </script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>

/*
每个函数都只有一句话!
其实都是从我的另一篇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无关,且可适应各种语言版本的

*/

--周日算作(上一)周的 最后一天

createfunctionudf_WeekOfYear(@datedatetime)
--求@date所在周是当年的第几周
--周日算作(上一)周的 最后一天
--用于按周 汇总Groupby时,不要有跨年数据,或者同时Groupbyyear
--groupbyyear(date),month(date),dbo.udf_WeekOfYear(date),dbo.udf_WeekOfMonth(date)
returnsint
as
begin
return
(selectdatediff(week
                ,casewhen(@@datefirst+datepart(weekday,dateadd(day,0,datediff(day,0,dateadd(year,datediff(year,0,@date),0)))))%7=1
                           thendateadd(day,-1,dateadd(day,0,datediff(day,0,dateadd(year,datediff(year,0,@date),0))))
                      elsedateadd(day,0,datediff(day,0,dateadd(year,datediff(year,0,@date),0)))--date所在年的第一天即:一月一号
                 end
                ,casewhen(@@datefirst+datepart(weekday,@date))%7=1
                           thendateadd(day,-1,@date)
                      else@date
                 end
               )+1)
end

go

createfunctionudf_WeekOfMonth(@datedatetime)
--求@date所在周是当月的第几周
--周日算作(上一)周的 最后一天
--用于按周 汇总Groupby时,不要有跨月跨年数据,或者同时Groupbyyear,month
--groupbyyear(date),month(date),dbo.udf_WeekOfYear(date),dbo.udf_WeekOfMonth(date)
returnsint
as
begin
return
     (selectdatediff(week
                ,casewhen(@@datefirst+datepart(weekday,dateadd(month,datediff(month,0,@date),0)))%7=1
                           thendateadd(month,datediff(month,0,@date),0)-1
                      elsedateadd(month,datediff(month,0,@date),0)共5页  1    
<script type="text/javascript"> </script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>

<script type="text/javascript"> </script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>