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...
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.
5 Answers
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.
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')
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'
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'
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))
)
Sarah Jenkins is a veteran tech journalist with over 12 years of experience covering artificial intelligence, mobile innovations, and digital ethics. Her insights have appeared in leading technology publications worldwide.
