T-SQL Between Dates Confusion
I am working with T-SQL in SQL Server 2000 and I have a table TRANSACTIONS which has a date column TRANDATE defined as DateTime, among many other columns which are irrelevant for this question..
The table is populated with transactions spanning many years. I ran into code, test, that has me confused. There is a simple SELECT, like this:
SELECT TRANDATE, RECEIPTNUMBER FROM TRANSACTIONS WHERE TRANDATE BETWEEN '12/01/2010' and '12/31/2010' ORDER BY TRANDATE
and its not returning two rows of data that I know are in that table.
With the statement above, the last row its returning, in order, has a TRANDATE of:
2010-12-31 00:00:00.000
When I modify the statement like below, I get the additional two rows for December 2010 that are in that table:
SELECT TRANDATE, RECEIPTNUMBER FROM TRANSACTIONS WHERE TRANDATE BETWEEN '12/01/2010 00:00:00' and '12/31/2010 23:59:59' ORDER BY TRANDATE
I have tried to find out why the BETWEEN operator doesnt include ALL rows for the 24 period in 12/31/2010 when using the first SELECT, above. And why does it need to have the explicit hours added to the SELECT statement as in the second, modified, statement to get it to pull the correct number of rows out?
Is it because of the way TRANDATE is defined as "DATETIME"?
Based on this finding, I think that am going to have to go through all of this old code because these BETWEEN operators are littered throughout this old system and it seems like its not pulling all of the data properly. I just wanted clarification from some folks first. Thanks!
Answer
A date is a point in time, not a time span.
'12/31/2010' is a point, too. Namely, it's the midnight of the 31st of December.
Everything that happened after this point is ignored.
That's exactly the behaviour you want (even if you haven't realised that yet).
Do not think that when you choose to omit the time part, it is magically assumed to be "any". It's going to be "all zeroes", that is, the midnight.
If you want to include the entire day in your query without having to specify 23:59:59 (which, by the way, excludes the last second of the day, between the moment 23:59:59 of the current day and the moment 00:00:00 of the next day), you can do that either by using strict inequalities (>, <) bounded by the first points of time you don't want:
WHERE TRANDATE >='12/01/2010 00:00:00' and TRANDATE < '01/01/2011'
or by comparing date values casted to DATE:
WHERE CAST(TRANDATE AS DATE) between '12/01/2010' and '12/31/2010'
(it is okay to put this type of cast in a WHERE clause, it is sargable).