Datetime BETWEEN语句在SQL Server中不起作用 [英] Datetime BETWEEN statement not working in SQL Server
问题描述
我有以下查询,
SELECT * FROM LOGS
WHERE CHECK_IN BETWEEN CONVERT(datetime,'2013-10-17') AND CONVERT(datetime,'2013-10-18')
此查询不返回任何结果,但以下查询返回结果,
this query not returning any result, but the following query return the result,
SELECT * FROM LOGS WHERE CHECK_IN >= CONVERT(datetime,'2013-10-17')
为什么第一个查询不返回任何结果?如果我有任何错误,请纠正我.
why the first query not returning any result? If I did any mistake pls correct me.
推荐答案
您是否有与日期关联的时间? BETWEEN是包含性的,但是当您将2013-10-18转换为日期时,它将变为2013-10-18 00:00:000.00.在18日的第一秒之后记录的所有内容都不会使用BETWEEN显示,除非您包含时间值.
Do you have times associated with your dates? BETWEEN is inclusive, but when you convert 2013-10-18 to a date it becomes 2013-10-18 00:00:000.00. Anything that is logged after the first second of the 18th will not shown using BETWEEN, unless you include a time value.
尝试:
SELECT * FROM LOGS WHERE CHECK_IN BETWEEN CONVERT(datetime,'2013-10-17') AND CONVERT(datetime,'2013-10-18 23:59:59:999')
如果要搜索整天的18天.
if you want to search the entire day of the 18th.
SQL DATETIME字段以毫秒为单位.所以我在字段中添加了999.
SQL DATETIME fields have milliseconds. So I added 999 to the field.
这篇关于Datetime BETWEEN语句在SQL Server中不起作用的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!