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

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

Can you post the results from select top 5 CHECK_IN from LOGS where CHECK_IN >= convert(datetime, '2013-10-17') ? — kgu87, Oct 18 '13 at 14:19

score 55 · Accepted Answer · edited Jul 01 '21 at 09:18

55

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.

edited Jul 01 '21 at 09:18

StepUp

36,391
15
88
148

answered Oct 18 '13 at 14:30

Skerkles

1,123
7
15

I tried this approach but it keeps accusing syntax error, even for this exact query tells it have syntax error. can be reproduced using: https://es.rakko.tools/tools/36/ – intmarinoreturn0 Jan 03 '22 at 21:51
rakko.tools looks like a MySQL site - I'd check the MySQL questions. https://stackoverflow.com/questions/3822648/how-do-i-query-between-two-dates-using-mysql looks light it might help with the required syntax. – Skerkles Jan 10 '22 at 20:30

podiluska · Answer 2 · 2015-12-13T19:26:20.453

6

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')

edited Dec 13 '15 at 19:26

answered Oct 18 '13 at 14:15

podiluska

50,950
7
98
104

score 4 · Answer 3 · answered Sep 06 '17 at 14:28

4

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://learn.microsoft.com/en-us/sql/t-sql/data-types/date-transact-sql

answered Sep 06 '17 at 14:28

maurox

1,254
1
19
33

score 2 · Answer 4 · answered Oct 18 '13 at 14:15

2

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'

answered Oct 18 '13 at 14:15

apomene

14,282
9
46
72

NetIntel · Answer 5 · 2014-08-22T15:08:15.393

0

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

edited Aug 22 '14 at 15:08

answered Aug 22 '14 at 14:57

NetIntel

1
1

score 0 · Answer 6 · edited Dec 23 '22 at 12:04

DROP TABLE  IF EXISTS #TB_PERIODO_TEMP CREATE TABLE #TB_PERIODO_TEMP (DATA DATETIME)
INSERT INTO #TB_PERIODO_TEMP VALUES
('22-12-2022 00:00:00'),
('22-12-2022 23:59:59'),
('23-12-2022 00:00:00'),
('23-12-2022 23:59:59')

SELECT * FROM #TB_PERIODO_TEMP -- ALL

SELECT * FROM #TB_PERIODO_TEMP WHERE DATA 
    BETWEEN '22-12-2022' AND '23-12-2022' --OLÉ!!!

--BETWEEN CONSIDERING PERIODS WITH HOURS O/
SELECT * FROM #TB_PERIODO_TEMP WHERE DATA 
    BETWEEN '22-12-2022' AND '23-12-2022 23:59:59:999'

Datetime BETWEEN statement not working in SQL Server

6 Answers6

Linked