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屋！