XML Feeds
Do you use between to return data that has dates? Do you know that between will get everything since midnight from the first criteria and up to midnight exactly from the second criteria. If you do BETWEEN 2006-10-01 AND 2006-10-02 then all the values that are greater or equal than 2006-10-01 and less or equal to 2006-10-02 will be returned. So no values after 2006-10-02 midnight will be returned.
Let's test this out, first let's create this table
- CREATE TABLE SomeDates (DateColumn DATETIME)
Insert 2 values
- INSERT INTO SomeDates VALUES('2006-10-02 00:00:00.000')
- INSERT INTO SomeDates VALUES('2006-10-01 00:00:00.000')
Return everything between '2006-10-01' and '2006-10-02'
- SELECT *
- FROM SomeDates
- WHERE DateColumn BETWEEN '20061001' AND '20061002'
- ORDER BY DateColumn
This works without a problem
Let's add some more dates including the time portion
- INSERT INTO SomeDates VALUES('2006-10-02 00:01:00.000')
- INSERT INTO SomeDates VALUES('2006-10-02 00:00:59.000')
- INSERT INTO SomeDates VALUES('2006-10-02 00:00:01.000')
- INSERT INTO SomeDates VALUES('2006-10-01 00:01:00.000')
- INSERT INTO SomeDates VALUES('2006-10-01 00:12:00.000')
- INSERT INTO SomeDates VALUES('2006-10-01 23:00:00.000')
Return everything between '2006-10-01' and '2006-10-02'
- SELECT *
- FROM SomeDates
- WHERE DateColumn BETWEEN '20061001' AND '20061002'
- ORDER BY DateColumn
Here is where it goes wrong; for 2006-10-02 only the midnight value is returned the other ones are ignored
Now if we change 2006-10-02 to 2006-10-03 we get what we want
- SELECT *
- FROM SomeDates
- WHERE DateColumn BETWEEN '20061001' AND '20061003'
- ORDER BY DateColumn
Now insert a value for 2006-10-03 (midnight)
- INSERT INTO SomeDates VALUES('2006-10-03 00:00:00.000')
Run the query again
- SELECT *
- FROM SomeDates
- WHERE DateColumn BETWEEN '20061001' AND '20061003'
- ORDER BY DateColumn
We get back 2006-10-03 00:00:00.000, between will return the date if it is exactly midnight
If you use gt;= and lt; then you get exactly what you need
- SELECT *
- FROM SomeDates
- WHERE DateColumn >= '20061001' AND DateColumn < '20061003'
- ORDER BY DateColumn
- --Clean up
- DROP TABLE SomeDates
So be careful when using between because you might get back rows that you did not expect to get back and it might mess up your reporting if you do counts or sums
Denis has been working with SQL Server since version 6.5. Although he worked as an ASP/JSP/ColdFusion developer before the dot com bust, he has been working exclusively as a database developer/architect since 2002. In addition to English, Denis is also fluent in Croatian and Dutch, but he can curse in many other languages and dialects (just ask the SQL optimizer) He lives in Princeton, NJ with his wife and three kids.
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.