Monday, October 23, 2006

Do You Know How Between Works With Dates?

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 >= and < 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

6 comments:

Anonymous said...

Seems obvious to me. Your database field is a datetime, not a date field. Thus, '2006-10-02' is equivalent to '2006-10-02 00:00:00'. Try using:
DateColumn between '2006-10-01' and '2006-10-02 23:59:59'

SQL said...

That's true and I am aware of that. BTW SQL Server only has datetime columns not date columns (time is always included). I am pointing this out because I came across code like this many times and then people would spend hours to find why their reports were not correct. this is just a warning that's all

Hugo Kornelis said...

Hi anonymous,

Your recommendation to use DateColumn between '2006-10-01' and '2006-10-02 23:59:59' can fail.

For smalldatetime, 23:59:59 has to be rounded to the nearest full minute - and you're back at 2006-10-03, midnight.
For datetime, there's no rouding, but values from 2006-10-02 23:59:59.003 up to 2006-10-02 23:59:59.997 will be missed.

Also, neither '2006-10-02' nor '2006-10-03 23:59:59' are guaranteed safe date or datetime formats. Better use '20061002' (no dahses) and '2006-10-03T23:59:59' (dashes, colons, AND uppercase T).

TGKnIght said...

Good warning, but easily fixed

WHERE date(dbdate) BETWEEN '2006-10-01' AND '2006-10-02'

SQL said...

Conversion on the left side of an operator will force an table/index scan instead of an index seek. This will hurt performance,You can verify that here-->http://sqlservercode.blogspot.com/2005/12/sql-query-optimizations.html

evanx said...

as someone probably said already, if your operand is a timestamp, then your between values should be a timestamp too - if they are a date, they will be converted to a timestamp with time 00:00:00 - so in terms of dates, the right hand side will become exclusive, whereas usually it is inclusive.