Question

[SQL Server]从字符串转换为 datetime 时发生语法错误

sql="select count(id) as ones from [data] where emdate>'2009-01-01' backtime is not null and DATEADD(day,1,CONVERT(VarChar(100),backtime,23))<=arrivetime "这个句子也是，关键错误是这句：DATEADD(day,1,CONVERT(VarChar(100),backtime,23))<=arrivetime 其他的测试没有问题
数据库中 backtime 字段为字符串格式，arrivetime 是datetime 格式请求给与正确的语句，筛选 backtime 和 arrivetime 相差小于等于一天的数据集运行提示Microsoft OLE DB Provider for ODBC Drivers 错误 '80040e07' [Microsoft][ODBC SQL Server Driver][SQL Server]从字符串转换为 datetime 时发生语法错误。

Answer 1

可能是backtime 字段有非日期格式的值
用isdate(backtime) 先查查data 表 backtime 字段是否有非法值
如果输入表达式是有效的日期，那么 ISDATE 返回 1；否则，返回 0。

Answer 2

sql="select count(id) as ones from [data] where emdate>'2009-01-01' backtime is not null and DATEADD(day,1,backtime))<=arrivetime 如果varchar中存储的字串与日期格式相符，进行datetime运算时可以显示转换