fishmong3r fishmong3r - 4 months ago 17
SQL Question

SQL date comparison issue

How is it possible that the 1st query returns records for today and yesterday, but the 2nd one returns only for yesterday?

1st

SELECT
*
FROM
table
WHERE
DateTimeOfInsert >= '20160714'


2nd

SELECT
*
FROM
table
WHERE
DateTimeOfInsert >= '20160714'
AND
DateTimeOfInsert <= '20160715'


I can't use
BETWEEN
as it is allowed to provide only one of
start
/
end
dates.

Answer

You missed the Time. You are technically searching for 2016-07-14 00:00:00 to 2016-07-15 00:00:00, which only consists 2016-07-14 data.

If you need to find on both 2 days, then:

SELECT 
  * 
FROM 
  table
WHERE
    DateTimeOfInsert >= '2016-07-14 00:00:00' 
  AND 
    DateTimeOfInsert <= '2016-07-15 23:59:59'

or simply use this for the second condition

DateTimeOfInsert <= '20160716'

which implies between 2016-07-14 00:00:00 to 2016-07-16 00:00:00