求一SQL语句？

Question

select AppTime, WorkTime from wapprain where substring(AppTime,1,10)= '2007-03-18' 
AppTime              WorkTime             
-------------------- -------------------- 
2007-03-18 6:00      6:07
2007-03-18 6:24      6:36
2007-03-18 6:26      6:31
2007-03-18 6:43      6:44
2007-03-18 7:03      7:08
2007-03-18 7:11      7:17
2007-03-18 7:13      7:23
2007-03-18 7:39      7:42
2007-03-18 11:3      11:11
2007-03-18 11:28     11:47
2007-03-18 7:39      7:43
2007-03-18 7:56      8:07
2007-03-18 13:39     13:50
2007-03-18 8:09      8:16
2007-03-18 8:19      8:25
2007-03-18 8:36      8:40
2007-03-18 9:30      9:35
2007-03-18 9:44      9:51
2007-03-18 10:26     10:31
2007-03-18 14:22     14:30（所影响的行数为 20 行）
我想查 用AppTime的日期+WorkTime的时间 即2007-03-18 12:00到20:00之间的数据
 我用这句错的，该怎么改！
select AppTime, WorkTime from wapprain where convert(datetime,substring(AppTime,1,10)+' '+WorkTime) between '2007-03-18 12:00:00' and '2007-03-18 20:00:00'

Answer 1

select AppTime, WorkTime from wapprain where caset(substring(AppTime,1,10) + cast(WorkTime as varchar) as datetime) >= '2007-03-18 12:00:00:000' and  caset(substring(AppTime,1,10) + cast(WorkTime as varchar) as datetime) <= '2007-03-18 18:00:00:000'

Answer 2

select AppTime, WorkTime from wapprain where cast(substring(AppTime,1,10) + cast(WorkTime as varchar) as datetime) >= '2007-03-18 12:00:00:000' and  cast(substring(AppTime,1,10) + cast(WorkTime as varchar) as datetime) <= '2007-03-18 18:00:00:000'

Answer 3

select cast(convert(char(10) ,AppTime,120) + ' '+right('00'cast(WorkTime as varchar) ,4) as datetime)

Answer 4

select AppTime, WorkTime  from wapprain where cast(convert(char(10) ,AppTime,120) + ' '+right('00'cast(WorkTime as varchar) ,4) as datetime) between '2007-03-18 12:00:00' and '2007-03-18 20:00:00'

Answer 5

應該沒有問題的Select Cast(Substring('2007-03-18 14:22', 1, 10) + ' ' + '14:30' As DateTime)
Select Convert(DateTime, Substring('2007-03-18 14:22', 1, 10) + ' ' + '14:30')
--Result
/*
2007-03-18 14:30:00.000
2007-03-18 14:30:00.000
*/

Answer 6

是你的WorkTime有问题?
试试这个select * from wapprain
where dateadd(minute,cast(left(worktime,charindex(':',worktime)-1) as int)*60+cast(substring(worktime,charindex(':',worktime)+1,2) as int),apptime) between '2007-03-18 12:00:00' and '2007-03-18 20:00:00'

Answer 7

select AppTime, WorkTime from wapprain where cast(substring(AppTime,1,10) + cast(WorkTime as varchar) as datetime) >= '2007-03-18 12:00:00:000' and  cast(substring(AppTime,1,10) + cast(WorkTime as varchar) as datetime) <= '2007-03-18 18:00:00:000'
我用这句还是出现：从字符串转换为 datetime 时发生语法错误。select * from wapprain
where dateadd(minute,cast(left(worktime,charindex(':',worktime)-1) as int)*60+cast(substring(worktime,charindex(':',worktime)+1,2) as int),apptime) between '2007-03-18 12:00:00' and '2007-03-18 20:00:00'
向 substring 函数传递了无效的 length 参数。

Answer 8

select AppTime, WorkTime  from wapprain where cast(convert(char(10) ,AppTime,120) + ' '+right('00'cast(WorkTime as varchar) ,4) as datetime) between '2007-03-18 12:00:00' and '2007-03-18 20:00:00''cast' 附近有语法错误。

Answer 9

用以下語句將錯誤的日期取出來Select AppTime From wapprain Where IsDate(AppTime) = 0如果以上查詢有結果的話，手動將其修改掉