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 时发生语法错误。
这里你没改呀: and datediff(hour,b.rjsj,b.cjsj)<600 then 1.5
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
首先看一下你这两个字段的数据类型是否是datetime类型,如果不是SELECT * FROM ( SELECT ISDATE(a.rjsj) AS dateChk FROM ry_day_info ) a WHERE a.dateChk=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, 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,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了 散了。
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 时发生语法错误。
http://blog.csdn.net/dba_huangzj/article/details/7657979
and datediff(hour,b.rjsj,b.cjsj)<600 then 1.5
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 ISDATE(a.rjsj) AS dateChk
FROM ry_day_info ) a WHERE a.dateChk=0查一下是否存在非日期格式的数据,然后你就知道哪错了。
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
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
我试了 可是还是报错啊 服务器: 消息 241,级别 16,状态 1,行 1
从字符串转换为 datetime 时发生语法错误
不能直接用 cast么
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
---------*/
不能直接用 cast么 varchar类型报错证明你的字段有非日期格式的数据,所以你的语句报错,有两种方案,一统一数据,二在查询语句中先排除非日期格式的数据
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了 散了。