SQL date filtering

Question

Just a fairly general date filtering question...

I've got a date field that contains YYYYMMDD (no time). When looking for transactions in the current month I get a different result if I do...

and MyDateField between '2017-03-01' and '2017-03-31'

to if I do...

and Year(MyDateField) = '2017' and Month(MyDateField) = '3'

It looks like the 'between' method is excluding anything that happened on the 1st March (the other transactions are future dated).

Why is this?

These are the results from the 'between' method...

and these are the 'Year/Month' results...

The data is stored as an nvarchar(8) - should I be converting to date first?

Could you post some sample of how your date datatype looks like and its datatype — TheGameiswar, Mar 01 '17 at 10:15
If it looks like a `DATE`, smells like a `DATE`, and quacks like a `DATE` - then yes, you **SHOULD** make that column a `DATE` datatype! ***ALWAYS*** use the most appropriate datatype - that's what they're there for! And storing a date into a string is **not** the most appropriate way to store this information..... — marc_s, Mar 01 '17 at 10:18
Well, your last sentece says it all. Don't store dates as strings, and you will save yourself from a lot of pain. Read Aaron Bertrand's [Bad habits to kick : choosing the wrong data type](https://sqlblog.org/2009/10/12/bad-habits-to-kick-choosing-the-wrong-data-type) — Zohar Peled, Mar 01 '17 at 10:19
Yep, that's it - unfortunately, it's coming from a badly designed proprietary system that stores a date as an nvarchar! — dazzathedrummer, Mar 01 '17 at 10:24

score 5 · Answer 1 · edited Jul 29 '20 at 18:30

BETWEEN is inclusive.

and MyDateField between '2017-03-01' and '2017-03-31'

is the same as:

and MyDateField >= '2017-03-01' 
and MyDateField <= '2017-03-31'

The only explanation I have is that the type of MyDateField is not date, but datetime or something similar that may have a time component.

And some values in your table actually have this non-zero time component.

So, you query should look like this:

and MyDateField >= '20170301' 
and MyDateField < '20170401'

For more details see Bad habits to kick : mis-handling date / range queries by Aaron Bertrand.

Well, after you said that the column type is nvarchar(8), then it becomes obvious that comparison of strings '2017-03-01' and '20170301' can't lead to anything good.

When you use BETWEEN, you compare strings, not dates. When you use YEAR and MONTH, the server converts your nvarchar(8) values into dates behind the scene (and ruins performance).

You may store dates as nvarchar(8), but then you need to compare them to the strings in the same format, like this (without dashes):

and MyDateField >= '20170301' 
and MyDateField <= '20170331'

It would be better to store them as char(8) and it would be significantly better to store them as date, though. 3 bytes for date vs. at least 17 bytes for nvarchar(8).

See Bad habits to kick : choosing the wrong data type.

Yep - nvarchar is the problem! – dazzathedrummer Mar 01 '17 at 10:25 — dazzathedrummer, Mar 01 '17 at 10:25

SQL date filtering

1 Answers1