http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=FindDataBetweenDates&referringTitle=Home
tracks data chronologically with a date and time, is to extract portions of
that data between two time periods. A common mistake in SQL is to use
the between operator for datetime comparisons. This can lead to an issue
of double counting certain rows of data which is incorrect.
For example, from the following data:
| orderid | SubTotal | Shipping | OrderTotal | OrderDate |
|---|---|---|---|---|
| 1 | 100.00 | 0.00 | 100.00 | 2007-12-22 23:06:16.820 |
| 2 | 75.00 | 5.95 | 80.95 | 2007-12-22 23:06:59.787 |
| 3 | 25.00 | 5.95 | 30.95 | 2007-12-22 23:09:18.083 |
| 4 | 50.00 | 5.95 | 55.95 | 2007-12-22 23:13:15.120 |
| 5 | 50.00 | 5.95 | 55.95 | 2007-12-22 23:28:49.860 |
| 6 | 50.00 | 10.45 | 60.45 | 2007-12-22 23:37:44.593 |
| 7 | 200.00 | 13.70 | 213.70 | 2007-12-22 23:56:10.533 |
| 8 | 25.00 | 5.95 | 30.95 | 2007-12-23 00:00:00.000 |
| 9 | 50.00 | 5.95 | 55.95 | 2007-12-23 00:01:19.477 |
| 10 | 50.00 | 5.95 | 55.95 | 2007-12-23 00:08:43.227 |
Create Sample Data
-- Suppress data loading messages SET NOCOUNT ON -- Create Sample Data using a Table Varable DECLARE @Orders TABLE (Orderid int identity primary key, subtotal decimal(18,2), shipping decimal(18,2), Ordertotal decimal(18,2), Orderdate datetime) -- Load Sample Data INSERT INTO @Orders VALUES (100.00, 0.00, 100.00, 'Dec 22 2007 11:06:16:820PM') INSERT INTO @Orders VALUES (75.00, 5.95, 80.95, 'Dec 22 2007 11:06:59:787PM') INSERT INTO @Orders VALUES (25.00, 5.95, 30.95, 'Dec 22 2007 11:09:18:083PM') INSERT INTO @Orders VALUES (50.00, 5.95, 55.95, 'Dec 22 2007 11:13:15:120PM') INSERT INTO @Orders VALUES (50.00, 5.95, 55.95, 'Dec 22 2007 11:28:49:860PM') INSERT INTO @Orders VALUES (50.00, 10.45, 60.45, 'Dec 22 2007 11:37:44:593PM') INSERT INTO @Orders VALUES (200.00, 13.70, 213.70, 'Dec 22 2007 11:56:10:533PM') INSERT INTO @Orders VALUES (25.00, 5.95, 30.95, 'Dec 23 2007 12:00:00:000AM') INSERT INTO @Orders VALUES (50.00, 5.95, 55.95, 'Dec 23 2007 12:01:19:477AM') INSERT INTO @Orders VALUES (50.00, 5.95, 55.95, 'Dec 23 2007 12:08:43:227AM')
If you want to find all the orders for 12/22/2007, there are a number of ways
to do it. At first you may consider using a BETWEEN 12/22/2007 AND 12/22/2007:
Example using BETWEEN and the same date
--Query to Retrieve Desired Data DECLARE @Orderdate datetime SET @Orderdate = '2007/12/22' SELECT * FROM @Orders WHERE Orderdate BETWEEN @Orderdate AND @Orderdate -- Results Orderid subtotal shipping Ordertotal Orderdate -------- --------- --------- ------------ -----------------------
Since this returns no data, you might then try to use DATEADD, and look for
rows BETWEEN 12/22/2007 AND 12/23/2007 like the example below:
Example using BETWEEN and the problem it can lead to
--Query to Retrieve Desired Data DECLARE @Orderdate datetime SET @Orderdate = '2007/12/22' SELECT * FROM @Orders WHERE Orderdate BETWEEN @Orderdate AND DATEADD(dd, 1, @Orderdate) -- Results Orderid subtotal shipping Ordertotal Orderdate -------- --------- --------- ------------ ----------------------- 1 100.00 0.00 100.00 2007-12-22 23:06:16.820 2 75.00 5.95 80.95 2007-12-22 23:06:59.787 3 25.00 5.95 30.95 2007-12-22 23:09:18.083 4 50.00 5.95 55.95 2007-12-22 23:13:15.120 5 50.00 5.95 55.95 2007-12-22 23:28:49.860 6 50.00 10.45 60.45 2007-12-22 23:37:44.593 7 200.00 13.70 213.70 2007-12-22 23:56:10.533 8 25.00 5.95 30.95 2007-12-23 00:00:00.000 SET @Orderdate = '2007/12/23' SELECT * FROM @Orders WHERE Orderdate BETWEEN @Orderdate AND DATEADD(dd, 1, @Orderdate) -- Results Orderid subtotal shipping Ordertotal Orderdate -------- --------- --------- ------------ ----------------------- 8 25.00 5.95 30.95 2007-12-23 00:00:00.000 9 50.00 5.95 55.95 2007-12-23 00:01:19.477 10 50.00 5.95 55.95 2007-12-23 00:08:43.227
As you can see Row #8 falls into both days for this example, and nothing is returned
for the first example. The reason for this is that when you provide a string with no
timestamp, and it is converted into a datetime datatype, it is set to the start of the day
which is midnight. In the first example, the query tells sql to look for an order exactly
at midnight only. The second query however, tells SQL to look from midnight 12/22 to
midnight 12/23, again this being the start of the day and not the end of it. In this case
you catch the order from midnight in the results for both 12/22 and 12/23.
The correct answer to this query is not to use BETWEEN, but to use the greater than or
equals operator (>=) and the less than operator (<).
Problem solved with greater than and less than
DECLARE @Orderdate datetime SET @Orderdate = '2007/12/22' SELECT * FROM @Orders WHERE Orderdate >= @Orderdate AND Orderdate < DATEADD(dd, 1, @Orderdate) -- Results Orderid subtotal shipping Ordertotal Orderdate -------- --------- --------- ------------ ----------------------- 1 100.00 0.00 100.00 2007-12-22 23:06:16.820 2 75.00 5.95 80.95 2007-12-22 23:06:59.787 3 25.00 5.95 30.95 2007-12-22 23:09:18.083 4 50.00 5.95 55.95 2007-12-22 23:13:15.120 5 50.00 5.95 55.95 2007-12-22 23:28:49.860 6 50.00 10.45 60.45 2007-12-22 23:37:44.593 7 200.00 13.70 213.70 2007-12-22 23:56:10.533 SET @Orderdate = '2007/12/23' SELECT * FROM @Orders WHERE Orderdate >= @Orderdate AND Orderdate < DATEADD(dd, 1, @Orderdate) -- Results Orderid subtotal shipping Ordertotal Orderdate -------- --------- --------- ------------ ----------------------- 8 25.00 5.95 30.95 2007-12-23 00:00:00.000 9 50.00 5.95 55.95 2007-12-23 00:01:19.477 10 50.00 5.95 55.95 2007-12-23 00:08:43.227
