CODEWITHSUNDEEP
sqlsql-server
fishmong3r
fishmong3r

Reputation: 1434

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.

Upvotes: 2

Views: 102

Answers (2)

Igoranze
Igoranze

Reputation: 1486

Why not use the BETWEEN statement?

Your query should look something like this:

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

Upvotes: 0

kurakura88
kurakura88

Reputation: 2305

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

Upvotes: 4

Related Questions