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
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
Subscribe to:
Post Comments (Atom)
6 comments:
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'
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
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).
Good warning, but easily fixed
WHERE date(dbdate) BETWEEN '2006-10-01' AND '2006-10-02'
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
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.
Post a Comment