dazzathedrummer dazzathedrummer -4 years ago 141
SQL Question

SQL date filtering

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

20170303
20170327
20170309
20170324
20170331
20170306


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

20170303
20170327
20170309
20170324
20170301
20170331
20170306
20170301


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

Answer Source

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 significantly better to store them as date, though. See Bad habits to kick : choosing the wrong data type.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download