分类目录

链接

2012年二月
« 1月   3月 »
 12345
6789101112
13141516171819
20212223242526
272829  

近期文章

热门标签

博主推荐

现在位置:    首页 > SQL Server > 正文
计算任意两个时间之间的星期几的次数
SQL Server 暂无评论 阅读(2,502)

任意两个时间之间的星期几的次数-横.sql

SQL code
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_weekdaycount]') and xtype in (N'FN', N'IF', N'TF')) drop function [dbo].[f_weekdaycount] GO /*--计算任意两个时间之间的星期几的次数(横向显示) 本方法直接判断 @@datefirst 做对应处理 不受 sp_language 及 set datefirst 的影响 /*--调用示例 select * from f_weekdaycount('2004-9-01','2004-9-02') --*/ create function f_weekdaycount( @dt_begin datetime, @dt_end datetime )returns table as return( select 跨周数 ,周一=case a when -1 then case when 1 between b and c then 1 else 0 end when 0 then case when b<=1 then 1 else 0 end +case when c>=1 then 1 else 0 end else a+case when b<=1 then 1 else 0 end +case when c>=1 then 1 else 0 end end ,周二=case a when -1 then case when 2 between b and c then 1 else 0 end when 0 then case when b<=2 then 1 else 0 end +case when c>=2 then 1 else 0 end else a+case when b<=2 then 1 else 0 end +case when c>=2 then 1 else 0 end end ,周三=case a when -1 then case when 3 between b and c then 1 else 0 end when 0 then case when b<=3 then 1 else 0 end +case when c>=3 then 1 else 0 end else a+case when b<=3 then 1 else 0 end +case when c>=3 then 1 else 0 end end ,周四=case a when -1 then case when 4 between b and c then 1 else 0 end when 0 then case when b<=4 then 1 else 0 end +case when c>=4 then 1 else 0 end else a+case when b<=4 then 1 else 0 end +case when c>=4 then 1 else 0 end end ,周五=case a when -1 then case when 5 between b and c then 1 else 0 end when 0 then case when b<=5 then 1 else 0 end +case when c>=5 then 1 else 0 end else a+case when b<=5 then 1 else 0 end +case when c>=5 then 1 else 0 end end ,周六=case a when -1 then case when 6 between b and c then 1 else 0 end when 0 then case when b<=6 then 1 else 0 end +case when c>=6 then 1 else 0 end else a+case when b<=6 then 1 else 0 end +case when c>=6 then 1 else 0 end end ,周日=case a when -1 then case when 0 between b and c then 1 else 0 end when 0 then case when b<=0 then 1 else 0 end +case when c>=0 then 1 else 0 end else a+case when b<=0 then 1 else 0 end +case when c>=0 then 1 else 0 end end from( select 跨周数=case when @dt_begin<@dt_end then (datediff(day,@dt_begin,@dt_end)+7)/7 else (datediff(day,@dt_end,@dt_begin)+7)/7 end ,a=case when @dt_begin<@dt_end then datediff(week,@dt_begin,@dt_end)-1 else datediff(week,@dt_end,@dt_begin)-1 end ,b=case when @dt_begin<@dt_end then (@@datefirst+datepart(weekday,@dt_begin)-1)%7 else (@@datefirst+datepart(weekday,@dt_end)-1)%7 end ,c=case when @dt_begin<@dt_end then (@@datefirst+datepart(weekday,@dt_end)-1)%7 else (@@datefirst+datepart(weekday,@dt_begin)-1)%7 end)a ) go

本文版权归数据库之家所有,转载引用请完整注明以下信息:
本文作者:Bruce
本文地址:计算任意两个时间之间的星期几的次数 | 数据库之家

发表评论

留言无头像?