sql-serversql-server-2008between

Datetime BETWEEN statement not working in SQL Server


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 >= CONVERT(datetime,'2013-10-17')

why the first query not returning any result? If I did any mistake pls correct me.


Solution

  • 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:998')
    

    if you want to search the entire day of the 18th. I set miliseconds to 998 because SQL Server was pulling in 2013-10-19 00:00:00:0000 in the query.

    SQL DATETIME fields have milliseconds. So I added 999 to the field.