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.
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.