Datetime BETWEEN statement not working in SQL Server

Question

I have the following query   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  gt   CONVERT datetime  2013-10-17     why the first query not returning any result  If I did any mistake pls correct me

User · Answer

Does the second query return any results from the 17th, or just from the 18th?

The first query will only return results from the 17th, or midnight on the 18th.

Try this instead

select * 
from LOGS 
where check_in >= CONVERT(datetime,'2013-10-17') 
and check_in< CONVERT(datetime,'2013-10-19')

User · Answer

From Sql Server 2008 you have  date  format    So you can use   SELECT   FROM LOGS WHERE CONVERT date  CHECK IN   BETWEEN  2013-10-18  AND  2013-10-18    https   docs microsoft com en-us sql t-sql data-types date-transact-sql

User · Answer

You don t have any error in either of your queries  My guess is the following    No records exists between 2013-10-17  and  2013-10-18  the records the second query returns you exist after  2013-10-18

User · Answer

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   Try   SELECT   FROM LOGS WHERE CHECK IN BETWEEN CONVERT datetime  2013-10-17   AND CONVERT datetime  2013-10-18 23 59 59 999     if you want to search the entire day of the 18th   SQL DATETIME fields have milliseconds  So I added 999 to the field

User · Answer

You need to convert the date field to varchar to strip out the time  then convert it back to datetime  this will reset the time to  00 00 00 000    SELECT   FROM  TableName  WHERE               convert datetime convert varchar GETDATE   1             between           convert datetime convert varchar  StartDate  1             and            convert datetime convert varchar  EndDate  1

[sql-server] Datetime BETWEEN statement not working in SQL Server

Examples related to sql-server

Examples related to sql-server-2008

Examples related to between