Datetime BETWEEN statement does not work 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 returns no result, but the next query returns the result,

 SELECT * FROM LOGS WHERE CHECK_IN >= CONVERT(datetime,'2013-10-17') 

why does the first query return no result? If I was wrong, correct me.

Answer 1

Do you have time associated with your dates? BETWEEN is included, but when you convert 2013-10-18 to a date, it becomes 2013-10-18 00: 00: 000.00. Anything recorded after the first second of the 18th will not be displayed 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 all 18th day.

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

Answer 2

Does the second query get any results from the 17th or from the 18th?

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

Try this instead

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

Answer 3

You have no errors in any of your requests. I assume the following:

• There are no entries between 2013-10-17 and 2013-2018.
• records of the second request return you after "2013-10-18"

Answer 4

You need to convert the date field to varchar in order to cross out the time, and 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)) ) 

Answer 5

From Sql Server 2008, you have a "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