34,594
社区成员
发帖
与我相关
我的任务
分享
select *
from hr_leave_form f
where user_id = 133
and delete_flag = 0
and convert(smalldatetime,start_date + ' ' + start_time) <= convert(smalldatetime,str(20130311) + ' 11:00')
and convert(smalldatetime,end_date + ' ' + end_time) >= convert(smalldatetime,str(20130311)+ ' 10:00')
declare @tab table(a smalldatetime,b varchar(10))
insert into @tab values('20130429 10:00',' :00')
select a+b from @tab where ISDATE(b)=1 and CONVERT(smalldatetime,a+b)<=CAST(getdate() as smalldatetime)
/*
(1 行受影响)
消息 295,级别 16,状态 3,第 4 行
将字符串转换为 smalldatetime 数据类型时失败。
*/
[/quote]
这种方式也不是很好使,也是会有可能出现错误的,比如这位写的例子
应该用case when 的方式去处理最恰当了
declare @tab table(a smalldatetime,b varchar(10))
insert into @tab values('2013-04-28 00:00:00',':00'),('2013-04-29 00:00:00','10:00')
select * from @tab where CONVERT(smalldatetime,a+case when ISDATE(b)=1 then b else '00:00' end)<=CAST(getdate() as smalldatetime)
declare @tab table(a smalldatetime,b varchar(10))
insert into @tab values('20130429 10:00',' :00')
select a+b from @tab where ISDATE(b)=1 and CONVERT(smalldatetime,a+b)<=CAST(getdate() as smalldatetime)
/*
(1 行受影响)
消息 295,级别 16,状态 3,第 4 行
将字符串转换为 smalldatetime 数据类型时失败。
*/
select *
from hr_leave_form f
where user_id = 133
and delete_flag = 0
and convert(smalldatetime,start_date + ' ' + start_time) <= convert(smalldatetime,str(20130311) + ' 11:00')
and convert(smalldatetime,end_date + ' ' + end_time) >= convert(smalldatetime,str(20130311)+ ' 10:00')