- 浏览: 21487656 次
- 性别:
- 来自: 杭州
最新评论
-
ZY199266:
配置文件还需要额外的配置ma
Android 客户端通过内置API(HttpClient) 访问 服务器(用Spring MVC 架构) 返回的json数据全过程 -
ZY199266:
我的一访问为什么是 /mavenwebdemo/WEB-I ...
Android 客户端通过内置API(HttpClient) 访问 服务器(用Spring MVC 架构) 返回的json数据全过程 -
lvgaga:
我又一个问题就是 如果像你的这种形式写。配置文件还需要额外的 ...
Android 客户端通过内置API(HttpClient) 访问 服务器(用Spring MVC 架构) 返回的json数据全过程 -
lvgaga:
我的一访问为什么是 /mavenwebdemo/WEB-I ...
Android 客户端通过内置API(HttpClient) 访问 服务器(用Spring MVC 架构) 返回的json数据全过程 -
y1210251848:
你的那个错误应该是项目所使用的目标框架不支持吧
log4net配置(web中使用log4net,把web.config放在单独的文件中)
T-SQL 2 Tips: 1.计算任意两日期之间的"周一"到"周日"分别各有几个! 2.根据出生日期计算精确年龄!
这两个小技巧,不写不知道,一写吓一跳!
都是看似简单,实际做起来就懵,得仔细想一想,才能写对!
凡是有日期运算的程序都要细心哦!
先说第二个:
2.根据出生日期精确计算年龄!
所谓计算精确年龄就是: 生日差一天也不能长一岁!
大家常用,间隔年数算作年龄! 如果需求要精确,如: 保险 之类的,就粗了!
当然还可引申为根据入职日期计算精确的司龄,算加薪之类的需求!
我起初认为很简单,当年也写了好几遍才写对!高手们也被我晃点了数次几近晕倒!
不信有当年 2002-11-27 16:16:26 贴子为证:
《我的表中有字段出生年月日,请问怎样根据当前日期计算该人的年龄,并填充到表的相应字段内?》
http://search.csdn.net/expert/topicview1.asp?id=1210302
书归正传:
declare @Birthday smalldatetime
set @Birthday ='1949-10-01'
--多用一个日期变量,为了扩展: 计算将来或过去某一时刻的当时的精确年龄 的功能!
declare @ smalldatetime
set @ = getdate()
select datediff(year,@Birthday,getdate())
- case when datediff(day,dateadd(year,datediff(year,@Birthday,getdate()),@Birthday),getdate()) >= 0
then 0
else 1
end
T-SQL UDF:
createfunction GetAge(@BeginDate datetime,@EndDate datetime)
returns integer
begin
return (select datediff(year,@BeginDate,@EndDate) - case when datediff(day,dateadd(year,datediff(year,@BeginDate,@EndDate),@BeginDate),@EndDate) >= 0 then 0 else 1 end)
end
再说第一个:
1.计算任意两日期之间的 周一 到 周日 分别各有几个!
起因来自该贴:
《SQL语句求当月的上班天数》
http://community.csdn.net/Expert/TopicView1.asp?id=3291510
本来想偷懒 Google 一下,没有满意的结果,好像他自己也没底!思路也不一样!
《求一尖酸的算法》(有点儿同感,说起来简单,做起来就晕)
http://www.itpub.net/252645.html
顺便说一下,我写程序总想写的"更通用"一些,可能答非所问了!
由于 @@datefirst 可通过 set datefirst N 设定 导致 datepart(weekday,[date]) 不确定!
所以用 datename!
因为确定的日期是周几肯定是永远不变的!当然不随 datefirst 变!
所以更要用 datename!
另外按着中国人的习惯: 周日算作上周的最后一天!
如果是其他语言版本的 SQL Server 注意选用该语言版本的 周几 的形式!
declare @b datetime
declare @e datetime
set @b = '2004-07-29'
set @e = '2004-08-05'
select @b as 开始日期,@e as 结束日期,
datediff(week
,case when datename(weekday,@b) = '星期日' then @b - 1
else @b end
,case when datename(weekday,@e) = '星期日' then @e - 1
else @e end
) + 1 as 跨周数
,datediff(week
,case when datename(weekday,@b) = '星期日' then @b - 1
else @b end
,case when datename(weekday,@e) = '星期日' then @e - 1
else @e end
) + 1
- case when datename(weekday,@b) in ('星期二','星期三','星期四','星期五','星期六','星期日') then 1
else 0 end
as 周一个数
,datediff(week
,case when datename(weekday,@b) = '星期日' then @b - 1
else @b end
,case when datename(weekday,@e) = '星期日' then @e - 1
else @e end
) + 1
- case when datename(weekday,@b) in ('星期三','星期四','星期五','星期六','星期日') then 1
else 0 end
- case when datename(weekday,@e) in ('星期一') then 1
else 0 end
as 周二个数
,datediff(week
,case when datename(weekday,@b) = '星期日' then @b - 1
else @b end
,case when datename(weekday,@e) = '星期日' then @e - 1
else @e end
) + 1
- case when datename(weekday,@b) in ('星期四','星期五','星期六','星期日') then 1
else 0 end
- case when datename(weekday,@e) in ('星期一','星期二') then 1
else 0 end
as 周三个数
,datediff(week
,case when datename(weekday,@b) = '星期日' then @b - 1
else @b end
,case when datename(weekday,@e) = '星期日' then @e - 1
else @e end
) + 1
- case when datename(weekday,@b) in ('星期五','星期六','星期日') then 1
else 0 end
- case when datename(weekday,@e) in ('星期一','星期二','星期三') then 1
else 0 end
as 周四个数
,datediff(week
,case when datename(weekday,@b) = '星期日' then @b - 1
else @b end
,case when datename(weekday,@e) = '星期日' then @e - 1
else @e end
) + 1
- case when datename(weekday,@b) in ('星期六','星期日') then 1
else 0 end
- case when datename(weekday,@e) in ('星期一','星期二','星期三','星期四') then 1
else 0 end
as 周五个数
,datediff(week
,case when datename(weekday,@b) = '星期日' then @b - 1
else @b end
,case when datename(weekday,@e) = '星期日' then @e - 1
else @e end
) + 1
- case when datename(weekday,@b) in ('星期日') then 1
else 0 end
- case when datename(weekday,@e) in ('星期一','星期二','星期三','星期四','星期五') then 1
else 0 end
as 周六个数
,datediff(week
,case when datename(weekday,@b) = '星期日' then @b - 1
else @b end
,case when datename(weekday,@e) = '星期日' then @e - 1
else @e end
) + 1
- case when datename(weekday,@e) in ('星期一','星期二','星期三','星期四','星期五','星期六') then 1
else 0 end
as 周日个数
--哇塞终于搞定了,起初没想到的是: 竟然着实动了一番脑筋!
剩下的就是用的人可以为所欲为地将他变成 UDF SP (View) 等了!
如果你真正理解了我的思路,就可以用任何一种语言实现!
最后再作一下广告:
http://www.microshaoft.com
http://www.csdn.net/Develop/list_article.asp?author=playyuer
送你几个巨长的 SQL,也许狠有用,点一下,帮我加点儿人气:
T-SQL 生成 两个新的真正的公历年历
http://www.csdn.net/Develop/Read_Article.asp?Id=26447
T-SQL 生成一个简易的 公历年历 T-SQL 含日期所在月及年的周次
http://www.csdn.net/Develop/Read_Article.asp?Id=26083
http://www.microshaoft.com
http://www.csdn.net/Develop/list_article.asp?author=playyuer
截止到2004-08-21 :
一共三板斧:
1,2是邹建的
邹建的想法就是截头去尾
因为datediff不受 set datefirst 的影响,固定一周从周日到周六
所以我的想法也就是通过 @@datefirst 与 datepart(weekday,@dt) 的值,换算成固定的每周从0(星期天)到6(星期六)的数字,按下面的规则进行统计
datediff(week,@dt_begin,@dt_end) 的结果
如果是为0,表示没有跨周
那么统计的时候,就从 @dt_begin 到 @dt_end 之间的星期x有数,其他都没有数
如果是为1,表示跨周,但两个周都不完整
那么统计的时候,就分别统计
@dt_begin 到周六的星期X的数
+周日到 @dt_end 之间的星期x的数
如果是>1,表示跨周,而且中间有完整的周
那么统计的时候,就分别统计
@dt_begin 到周六的星期X的数
+周日到 @dt_end 之间的星期x的数
+完整的周数
declare @b datetime,@e datetime
select @b='2002-8-5',@e='2008-7-15'
--查询方式1(纵向)
select
项目='跨周数'
,值=case when @b<@e
then (datediff(day,@b,@e)+7)/7
else (datediff(day,@e,@b)+7)/7 end
union all
select a.a,case b.a
when -1 then case when a.b between b.b and b.c then 1 else 0 end
when 0 then case when b.b<=a.b then 1 else 0 end
+case when b.c>=a.b then 1 else 0 end
else b.a+case when b.b<=a.b then 1 else 0 end
+case when b.c>=a.b then 1 else 0 end
end
from(select a='星期一',b=1
union all select '星期二',2 union all select '星期三',3
union all select '星期四',4 union all select '星期五',5
union all select '星期六',6 union all select '星期日',0
)a,(select a=case when @b<@e
then datediff(week,@b,@e)-1
else datediff(week,@e,@b)-1 end
,b=case when @b<@e
then (@@datefirst+datepart(weekday,@b)-1)%7
else (@@datefirst+datepart(weekday,@e)-1)%7 end
,c=case when @b<@e
then (@@datefirst+datepart(weekday,@e)-1)%7
else (@@datefirst+datepart(weekday,@b)-1)%7 end
)b
--查询方式2(横向)
select @b as 开始日期,@e as 结束日期
,跨周数
,周一=case a
when -1 then case when b=1 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 b=2 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 b=3 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 b=4 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 b=5 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 b=6 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 b=0 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 @b<@e
then (datediff(day,@b,@e)+7)/7
else (datediff(day,@e,@b)+7)/7 end
,a=case when @b<@e
then datediff(week,@b,@e)-1
else datediff(week,@e,@b)-1 end
,b=case when @b<@e
then (@@datefirst+datepart(weekday,@b)-1)%7
else (@@datefirst+datepart(weekday,@e)-1)%7 end
,c=case when @b<@e
then (@@datefirst+datepart(weekday,@e)-1)%7
else (@@datefirst+datepart(weekday,@b)-1)%7 end)a
--3是playyuer的
select @b as 开始日期,@e as 结束日期,
datediff(week
,case when datename(weekday,@b) = '星期日' then @b - 1
else @b end
,case when datename(weekday,@e) = '星期日' then @e - 1
else @e end
) + 1 as 跨周数
,datediff(week
,case when datename(weekday,@b) = '星期日' then @b - 1
else @b end
,case when datename(weekday,@e) = '星期日' then @e - 1
else @e end
) + 1
- case when datename(weekday,@b) in ('星期二','星期三','星期四','星期五','星期六','星期日') then 1
else 0 end
as 周一个数
,datediff(week
,case when datename(weekday,@b) = '星期日' then @b - 1
else @b end
,case when datename(weekday,@e) = '星期日' then @e - 1
else @e end
) + 1
- case when datename(weekday,@b) in ('星期三','星期四','星期五','星期六','星期日') then 1
else 0 end
- case when datename(weekday,@e) in ('星期一') then 1
else 0 end
as 周二个数
,datediff(week
,case when datename(weekday,@b) = '星期日' then @b - 1
else @b end
,case when datename(weekday,@e) = '星期日' then @e - 1
else @e end
) + 1
- case when datename(weekday,@b) in ('星期四','星期五','星期六','星期日') then 1
else 0 end
- case when datename(weekday,@e) in ('星期一','星期二') then 1
else 0 end
as 周三个数
,datediff(week
,case when datename(weekday,@b) = '星期日' then @b - 1
else @b end
,case when datename(weekday,@e) = '星期日' then @e - 1
else @e end
) + 1
- case when datename(weekday,@b) in ('星期五','星期六','星期日') then 1
else 0 end
- case when datename(weekday,@e) in ('星期一','星期二','星期三') then 1
else 0 end
as 周四个数
,datediff(week
,case when datename(weekday,@b) = '星期日' then @b - 1
else @b end
,case when datename(weekday,@e) = '星期日' then @e - 1
else @e end
) + 1
- case when datename(weekday,@b) in ('星期六','星期日') then 1
else 0 end
- case when datename(weekday,@e) in ('星期一','星期二','星期三','星期四') then 1
else 0 end
as 周五个数
,datediff(week
,case when datename(weekday,@b) = '星期日' then @b - 1
else @b end
,case when datename(weekday,@e) = '星期日' then @e - 1
else @e end
) + 1
- case when datename(weekday,@b) in ('星期日') then 1
else 0 end
- case when datename(weekday,@e) in ('星期一','星期二','星期三','星期四','星期五') then 1
else 0 end
as 周六个数
,datediff(week
,case when datename(weekday,@b) = '星期日' then @b - 1
else @b end
,case when datename(weekday,@e) = '星期日' then @e - 1
else @e end
) + 1
- case when datename(weekday,@e) in ('星期一','星期二','星期三','星期四','星期五','星期六') then 1
else 0 end
as 周日个数
--======================
2004-09-11 再次汇总发现
--===========
declare @b datetime,@e datetime
set @b = '2004-9-14'
set @e = '2004-9-15'
select
@b as 开始日期
,@e as 结束日期
,datediff(week,@b,@e) + 1
+ case when (@@datefirst + datepart(weekday,@b)) % 7 = 1 then 1 else 0 end
- case when (@@datefirst + datepart(weekday,@e)) % 7 = 1 then 1 else 0 end as 跨周数
,datediff(week,@b,@e) + case when (@@datefirst + datepart(weekday,@b)) % 7 + case when (@@datefirst + datepart(weekday,@b)) % 7 = 0 then 7 else 0 end > 2 then 0 else 1 end - case when (@@datefirst + datepart(weekday,@e)) % 7 + case when (@@datefirst + datepart(weekday,@e)) % 7 = 0 then 7 else 0 end >= 2 then 0 else 1 end as 周一数
,datediff(week,@b,@e) + case when (@@datefirst + datepart(weekday,@b)) % 7 + case when (@@datefirst + datepart(weekday,@b)) % 7 = 0 then 7 else 0 end > 3 then 0 else 1 end - case when (@@datefirst + datepart(weekday,@e)) % 7 + case when (@@datefirst + datepart(weekday,@e)) % 7 = 0 then 7 else 0 end >= 3 then 0 else 1 end as 周二数
,datediff(week,@b,@e) + case when (@@datefirst + datepart(weekday,@b)) % 7 + case when (@@datefirst + datepart(weekday,@b)) % 7 = 0 then 7 else 0 end > 4 then 0 else 1 end - case when (@@datefirst + datepart(weekday,@e)) % 7 + case when (@@datefirst + datepart(weekday,@e)) % 7 = 0 then 7 else 0 end >= 4 then 0 else 1 end as 周三数
,datediff(week,@b,@e) + case when (@@datefirst + datepart(weekday,@b)) % 7 + case when (@@datefirst + datepart(weekday,@b)) % 7 = 0 then 7 else 0 end > 5 then 0 else 1 end - case when (@@datefirst + datepart(weekday,@e)) % 7 + case when (@@datefirst + datepart(weekday,@e)) % 7 = 0 then 7 else 0 end >= 5 then 0 else 1 end as 周四数
,datediff(week,@b,@e) + case when (@@datefirst + datepart(weekday,@b)) % 7 + case when (@@datefirst + datepart(weekday,@b)) % 7 = 0 then 7 else 0 end > 6 then 0 else 1 end - case when (@@datefirst + datepart(weekday,@e)) % 7 + case when (@@datefirst + datepart(weekday,@e)) % 7 = 0 then 7 else 0 end >= 6 then 0 else 1 end as 周五数
,datediff(week,@b,@e) + case when (@@datefirst + datepart(weekday,@b)) % 7 + case when (@@datefirst + datepart(weekday,@b)) % 7 = 0 then 7 else 0 end > 7 then 0 else 1 end - case when (@@datefirst + datepart(weekday,@e)) % 7 + case when (@@datefirst + datepart(weekday,@e)) % 7 = 0 then 7 else 0 end >= 7 then 0 else 1 end as 周六数
,datediff(week,@b,@e) + case when (@@datefirst + datepart(weekday,@b)) % 7 + case when (@@datefirst + datepart(weekday,@b)) % 7 = 0 then 7 else 0 end > 1 then 0 else 1 end - case when (@@datefirst + datepart(weekday,@e)) % 7 + case when (@@datefirst + datepart(weekday,@e)) % 7 = 0 then 7 else 0 end >= 1 then 0 else 1 end as 周日数
select @b as 开始日期,@e as 结束日期,
datediff(week
,case when datename(weekday,@b) = '星期日' then @b - 1
else @b end
,case when datename(weekday,@e) = '星期日' then @e - 1
else @e end
) + 1 as 跨周数
,datediff(week
,case when datename(weekday,@b) = '星期日' then @b - 1
else @b end
,case when datename(weekday,@e) = '星期日' then @e - 1
else @e end
) + 1
- case when datename(weekday,@b) in ('星期二','星期三','星期四','星期五','星期六','星期日') then 1
else 0 end
as 周一数
,datediff(week
,case when datename(weekday,@b) = '星期日' then @b - 1
else @b end
,case when datename(weekday,@e) = '星期日' then @e - 1
else @e end
) + 1
- case when datename(weekday,@b) in ('星期三','星期四','星期五','星期六','星期日') then 1
else 0 end
- case when datename(weekday,@e) in ('星期一') then 1
else 0 end
as 周二数
,datediff(week
,case when datename(weekday,@b) = '星期日' then @b - 1
else @b end
,case when datename(weekday,@e) = '星期日' then @e - 1
else @e end
) + 1
- case when datename(weekday,@b) in ('星期四','星期五','星期六','星期日') then 1
else 0 end
- case when datename(weekday,@e) in ('星期一','星期二') then 1
else 0 end
as 周三数
,datediff(week
,case when datename(weekday,@b) = '星期日' then @b - 1
else @b end
,case when datename(weekday,@e) = '星期日' then @e - 1
else @e end
) + 1
- case when datename(weekday,@b) in ('星期五','星期六','星期日') then 1
else 0 end
- case when datename(weekday,@e) in ('星期一','星期二','星期三') then 1
else 0 end
as 周四数
,datediff(week
,case when datename(weekday,@b) = '星期日' then @b - 1
else @b end
,case when datename(weekday,@e) = '星期日' then @e - 1
else @e end
) + 1
- case when datename(weekday,@b) in ('星期六','星期日') then 1
else 0 end
- case when datename(weekday,@e) in ('星期一','星期二','星期三','星期四') then 1
else 0 end
as 周五数
,datediff(week
,case when datename(weekday,@b) = '星期日' then @b - 1
else @b end
,case when datename(weekday,@e) = '星期日' then @e - 1
else @e end
) + 1
- case when datename(weekday,@b) in ('星期日') then 1
else 0 end
- case when datename(weekday,@e) in ('星期一','星期二','星期三','星期四','星期五') then 1
else 0 end
as 周六数
,datediff(week
,case when datename(weekday,@b) = '星期日' then @b - 1
else @b end
,case when datename(weekday,@e) = '星期日' then @e - 1
else @e end
) + 1
- case when datename(weekday,@e) in ('星期一','星期二','星期三','星期四','星期五','星期六') then 1
else 0 end
as 周日数
--查询方式2(横向)
select @b as 开始日期,@e as 结束日期
,跨周数
,周一数=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 @b<@e
then (datediff(day,@b,@e)+7)/7
else (datediff(day,@e,@b)+7)/7 end
,a=case when @b<@e
then datediff(week,@b,@e)-1
else datediff(week,@e,@b)-1 end
,b=case when @b<@e
then (@@datefirst+datepart(weekday,@b)-1)%7
else (@@datefirst+datepart(weekday,@e)-1)%7 end
,c=case when @b<@e
then (@@datefirst+datepart(weekday,@e)-1)%7
else (@@datefirst+datepart(weekday,@b)-1)%7 end)a
select
项目='跨周数'
,值=case when @b<@e
then (datediff(day,@b,@e)+7)/7
else (datediff(day,@e,@b)+7)/7 end
union all
select a.a,case b.a
when -1 then case when a.b between b.b and b.c then 1 else 0 end
when 0 then case when b.b<=a.b then 1 else 0 end
+case when b.c>=a.b then 1 else 0 end
else b.a+case when b.b<=a.b then 1 else 0 end
+case when b.c>=a.b then 1 else 0 end
end
from(select a='星期一',b=1
union all select '星期二',2 union all select '星期三',3
union all select '星期四',4 union all select '星期五',5
union all select '星期六',6 union all select '星期日',0
)a,(select a=case when @b<@e
then datediff(week,@b,@e)-1
else datediff(week,@e,@b)-1 end
,b=case when @b<@e
then (@@datefirst+datepart(weekday,@b)-1)%7
else (@@datefirst+datepart(weekday,@e)-1)%7 end
,c=case when @b<@e
then (@@datefirst+datepart(weekday,@e)-1)%7
else (@@datefirst+datepart(weekday,@b)-1)%7 end
)b
T-SQL UDF:
create function WeekdayDiff(@Weekday integer,@BeginDate datetime,@EndDate datetime)
-- @Weekday: 1= Monday , ... ,7= Sunday
returns integer
begin
return (select datediff(week,@BeginDate,@EndDate) + case when (@@datefirst + datepart(weekday,@BeginDate)) % 7 + case when (@@datefirst + datepart(weekday,@BeginDate)) % 7 = 0 then 7 else 0 end > @Weekday % 7 + 1 then 0 else 1 end - case when (@@datefirst + datepart(weekday,@EndDate)) % 7 + case when (@@datefirst + datepart(weekday,@EndDate)) % 7 = 0 then 7 else 0 end >= @Weekday % 7 + 1 then 0 else 1 end)
end
create function Weekday(@Date datetime)
returns integer
begin
--return: 1= Monday , ... ,7= Sunday
return (select (@@datefirst + datepart(weekday,@Date)) % 7
+ case when (@@datefirst + datepart(weekday,@Date)) % 7 < 2
then 6
else -1
end)
end
相关推荐
azkaban-sql-script-2.5.0.tar.gz
flume-ng-sql-source-1.5.2.jar从数据库中增量读取数据到hdfs中的jar包
<!-- https://mvnrepository.com/artifact/org.elasticsearch.plugin/x-pack-sql-jdbc --> <groupId>org.elasticsearch.plugin <artifactId>x-pack-sql-jdbc <version>6.8.15 </dependency>
1)rpm格式包安装配置 1.1 下载以rpm后缀名的包,以11.2.0.4.0 版本为例,其中基础包basic、sql*plus、devel包建议默认下载,其他包视情况而定: oracle-instantclient11.2-basic-11.2.0.4.0-1.x86_64.rpm --基础包...
Microsoft.SQL.Server.2012.High-Performance.T-SQL.Using.Window.Functions
ibatis标签提示工具 sql-map-2.dtd
1)rpm格式包安装配置 1.1 下载以rpm后缀名的包,以11.2.0.4.0 版本为例,其中基础包basic、sql*plus、devel包建议默认下载,其他包视情况而定: oracle-instantclient11.2-basic-11.2.0.4.0-1.x86_64.rpm --基础包...
1)rpm格式包安装配置 1.1 下载以rpm后缀名的包,以11.2.0.4.0 版本为例,其中基础包basic、sql*plus、devel包建议默认下载,其他包视情况而定: oracle-instantclient11.2-basic-11.2.0.4.0-1.x86_64.rpm --基础包...
oracle日期格式和java日期格式区别 HH24:mm:ss和HH24:mi:ss的区别 1.java 1)分钟用mm表示 24小时制: java(区分大小写):yyyy-MM-dd HH:mm:ss 12小时制: java(区分大小写):yyyy-MM-dd hh:mm:ss 2)...
MicrosoftSQLSERVER2008技术内幕 T-SQL查询的中英文和代码,全在这了
sql-map-config-2.dtd
flume连接数据库
您的第二个字段内容为:<%=rs.getString(2)%> <%out.print("数据库操作成功,恭喜你");%> <%rs.close(); stmt.close(); conn.close(); %> 在浏览器中输入: http://127.0.0.1:8080/myapp/first.jsp 若...
MS.SQL.Server.2008.技术内幕:T-SQL.查询.rar; SQLServer; 2008; 技术内幕; 查询;
文件太大,分4个压缩包上传的T-SQL编程入门经典.part1.rar、T-SQL编程入门经典.part2.rar 、T-SQL编程入门经典.part3.rar 、T-SQL编程入门经典.part4.rar
最新的正式发布版sqldeveloper-21.4.3.063.0100-x64,无需安装oracle客户端,直接使用,超级方便,性能稳定,功能强大,比plsql不知强了多少,而且无盗版烦恼,全免费正版,可验证md5. MD5: 9e091ededcad4344e21c5fd...
这是2010年1月12日从Oracle官方网站下载的最新版本,官网也就是从这一天开始才正式提供正确的下载路径,所以在此之前没有出现过这个2.1版。 注意: 除非内含jdk1.6.11,否则需要在系统中安装有jdk1.6.11或以上版本...
sqljdbc4-4.0.jar下载 《无需积分》,自行提取 Maven安装cmd指令 mvn install:install-file -DgroupId=com.microsoft.sqlserver -DartifactId=sqljdbc4 -Dversion=4.0 -Dpackaging=jar -Dfile=G:\迅雷下载\BB-sql...
microsoft.press.inside.microsoft.sql.server.2005.t-sql.querying.apr.2006
这是2010年1月12日从Oracle官方网站下载的最新版本,官网也就是从这一天开始才正式提供正确的下载路径,所以在此之前没有出现过这个2.1版。 注意: 除非内含jdk1.6.11,否则需要在系统中安装有jdk1.6.11或以上版本...