34,594
社区成员
发帖
与我相关
我的任务
分享
select
sum(case when datediff(n,b.rjsj,b.cjsj)>120 then 1
when datediff(n,b.rjsj,b.cjsj)>480 and datediff(hour,b.rjsj,b.cjsj)<600 then 1.5
when datediff(n,b.rjsj,b.cjsj)>600 then 2
else 0
end)as 下井次数
from ry_day_info a left join rk_day_info b
on a.name=b.xm and datediff(day,a.rjsj,b.rjsj)=0
select
sum(case when datediff(n, cast(b.rjsj as datetime), cast(b.cjsj as datetime))>600 then 2
when datediff(n, cast(b.rjsj as datetime), cast(b.cjsj as datetime))>480 then 1.5
when datediff(n, cast(b.rjsj as datetime), cast(b.cjsj as datetime))>120 then 1
else 0
end)as 下井次数
from ry_day_info a left join rk_day_info b
on a.name=b.xm and datediff(day, cast(a.rjsj as datetime), cast(b.rjsj as datetime))=0
select
sum(case when datediff(n, cast(b.rjsj as datetime),cast(b.cjsj as datetime))>120 then 1
when datediff(n,cast(b.rjsj as datetime),cast(b.cjsj as datetime))>480 and datediff(hour,cast(b.rjsj as datetime),cast(b.cjsj as datetime))<600 then 1.5
when datediff(n,cast(b.rjsj as datetime),cast(b.cjsj as datetime))>600 then 2
else 0
end)as 下井次数
from ry_day_info a left join rk_day_info b
on a.name=b.xm and datediff(day,cast(a.rjsj as datetime),cast(b.rjsj as datetime))=0
select
sum(case
when datediff(n, convert(varchar,cast(b.rjsj as datetime),120) ,convert(varchar,cast(b.cjsj as datetime),120) )>120 then 1
else 0
end)as 下井次数
from ry_day_info a left join rk_day_info b
on a.name=b.xm and datediff(day,cast(a.rjsj as datetime),cast(b.rjsj as datetime))=0
这样也不中 报错
服务器: 消息 241,级别 16,状态 1,行 1
从字符串转换为 datetime 时发生语法错误。
rjsj 例如2014-01-02 15:01:02.000 cjsj例如 2014-01-02 23:01:02.001
select
sum(case when datediff(n,b.rjsj,b.cjsj)>120 then 1
when datediff(n,b.rjsj,b.cjsj)>480 and datediff(hour,b.rjsj,b.cjsj)<600 then 1.5
when datediff(n,b.rjsj,b.cjsj)>600 then 2
else 0
end)as 下井次数
from ry_day_info a left join rk_day_info b
on a.name='李*'and b.xm='李*' and datediff(day,a.rjsj,b.rjsj)=0
把 数据库字段改为 datetime了 散了。
select datediff(n,
cast('2014-01-02 15:01:02.000' as datetime),
cast('2014-01-02 23:01:02.001' as datetime)
) as 井下时间
/*--结果--
井下时间
---------
480
---------*/
服务器: 消息 241,级别 16,状态 1,行 1
从字符串转换为 datetime 时发生语法错误
select
sum(case when datediff(n, cast(b.rjsj as datetime),cast(b.cjsj as datetime))>120 then 1
when datediff(n,cast(b.rjsj as datetime),cast(b.cjsj as datetime))>480 and datediff(hour,b.rjsj,b.cjsj)<600 then 1.5
when datediff(n,cast(b.rjsj as datetime),cast(b.cjsj as datetime))>600 then 2
else 0
end)as 下井次数
from ry_day_info a left join rk_day_info b
on a.name=b.xm and datediff(day,cast(a.rjsj as datetime),cast(b.rjsj as datetime))=0
服务器: 消息 241,级别 16,状态 1,行 1
从字符串转换为 datetime 时发生语法错误。