Why Does SQL Query of >= '5/1/2018' and <= '5/1/2018' only return rows of 2018-05-01 00:00:00?

Question

Ok I should know this after 20+ years of coding and querying databases, but it still doesn't make sense to me.

If I do this:

SELECT * FROM sometable WHERE createdate >= '5/1/2018' and createdate <= '5/1/2018'

I get:

6500061 2018-05-01 00:00:00
6500642 2018-05-01 00:00:00
6505091 2018-05-01 00:00:00
6505453 2018-05-01 00:00:00
6505469 2018-05-01 00:00:00
6505506 2018-05-01 00:00:00

But if I do this:

SELECT * FROM sometable WHERE createdate >= '5/1/2018 00:00:00' and createdate <= '5/1/2018 23:59:59'

I get:

6500061 2018-05-01 00:00:00
6500642 2018-05-01 00:00:00
6500652 2018-05-01 11:08:00
6500726 2018-05-01 11:25:00
6500736 2018-05-01 11:27:00
6504776 2018-05-01 15:05:00
6504778 2018-05-01 15:06:00
6505091 2018-05-01 00:00:00
6505371 2018-05-01 17:42:00
6505453 2018-05-01 00:00:00
6505469 2018-05-01 00:00:00
6505506 2018-05-01 00:00:00
6507371 2018-05-02 00:00:00
6508695 2018-05-02 00:00:00

Why doesn't SQL return everything that has that date no matter what the time is? Is there another function that I should be putting around my dates so I don't have to put the times in and just look at the dates?

Because it was designed so, a `datetime` always has a time and `5/1/2018` is the same as `5/1/2018 00:00:00`. But you can also use `>= '5/1/2018' and createdate < '5/2/2018'` — Tim Schmelter, May 04 '18 at 14:23
You can do something like `WHERE CONVERT(DATE, createdate) >= '5/1/2018' and CONVERT(DATE, createdate) <= '5/1/2018'`. Word of warning: If you have indexes on the `createdate` column, they wont be used if you do this. — HoneyBadger, May 04 '18 at 14:24
As an aside - I would recommend converting the string to a date more explicitly, convert (datetime, '20180501', 112) — Andrew, May 04 '18 at 14:25
@Andrew `'20180501'` is culture independent. There is no need for CAST/CONVERT at all for date literal in this format. — Lukasz Szozda, May 04 '18 at 14:26
Read the first few paragraphs of this: http://karaszi.com/the-ultimate-guide-to-the-datetime-datatypes. Then go back and read them again, they are important, and they answer your question — Richard Hansell, May 04 '18 at 14:48
@lad2025 - yes, but my habit is to always use explicit casts - the OP's dates were not in the ISO format however, `5/1/2018` and that's less than desirable outside of an explicit cast. — Andrew, May 04 '18 at 15:40

SQLChao · Accepted Answer · 2018-05-04T14:25:07.047

1

You have to put the times because your data is datetime. You can write a query where you could do what you are asking by casting the createdate as a date.

SELECT * FROM sometable WHERE CAST(createdate AS DATE) = '5/1/2018'

edited May 04 '18 at 14:25

answered May 04 '18 at 14:24

SQLChao

7,709
1
17
32

2

Non-sargable query, this will not be good on performance since an index will not support the query properly. – Andrew May 04 '18 at 14:25
I agree but giving him an option. – SQLChao May 04 '18 at 14:25
2

@Andrew - I've been led to believe that `CAST( AS DATE)` *is* Sargable. – Damien_The_Unbeliever May 04 '18 at 14:26
2

@Andrew - see e.g. [this question](https://dba.stackexchange.com/q/34047/6381) – Damien_The_Unbeliever May 04 '18 at 14:27
@Damien_The_Unbeliever, that's a good bit of information I wasn't expecting, thanks. – HoneyBadger May 04 '18 at 14:30
@Damien_The_Unbeliever - looks like it is, although with some caveats, will run some experiments - good info though. – Andrew May 04 '18 at 14:31
Thanks @Damien_The_Unbeliever. I had not seen that either. – SQLChao May 04 '18 at 14:32
Great information in all of the comments. We ended up using a statement with the CAST...obviously knowing some of the caveats as mentioned by @Damien_The_Unbeliever – Indy-Jones May 09 '18 at 02:52

Why Does SQL Query of >= '5/1/2018' and <= '5/1/2018' only return rows of 2018-05-01 00:00:00?

1 Answers1