Why am I missing rows when I select by date with Microsoft SQL Server
- Microsoft SQL Server does not support the data type of DATES!
- Microsoft SQL Server only supports the data type of DATETIME. Which is different from dates, it stores both the date and the time.
- Microsoft SQL Server has:
- SmallDateTime: From January 1, 1900 00:01 (AM), until June 6, 2079 23:59 (PM). A 1 minute precision. It does not keep track of the seconds.
- DateTime: From January 1, 1753, 00:00:00:03 (AM) until December 31, 9999 23:59:59:99:99 (PM). A 3.33 milliseconds precision. The values are rounded to increments of .000, .003, or .007 seconds.
- When people enter a date without a time, SQL Server interprets it as that date at 0:00:00.00.0.
- If all the dates do not have any time entered, then SQL server will properly retrieve the dates with BETWEEN or <=.
Data used
use sql911; go select sales_date, sales_qty, sales_price, emp_short from sales; go
sales_date sales_qty sales_price emp_short ----------------------- --------------------------------------- --------------------------------------- ---------- 2005-10-06 10:01:01.000 10.00 11.75 000310 2006-10-06 10:01:01.000 10.00 11.75 000310 2005-11-06 10:01:01.000 9.00 11.75 000110 2006-10-06 10:01:01.000 10.00 11.75 000310 2005-12-06 11:01:01.000 8.00 11.75 000010 2006-02-06 13:01:01.000 6.00 11.75 000190 2006-03-06 14:01:01.000 5.00 11.75 000110 2006-10-06 10:01:01.000 10.00 11.75 000190 2006-10-06 10:01:01.000 10.00 11.75 000190 2006-04-06 15:01:01.000 4.00 11.75 000030 2006-05-06 16:01:01.000 3.00 11.75 000010 2004-10-06 10:01:01.000 10.00 11.75 000310 2005-11-06 10:01:01.000 9.00 11.75 000010 2006-06-06 17:01:01.000 2.00 11.75 000190 2006-01-06 12:01:01.000 7.00 11.75 000310 2006-02-06 13:01:01.000 6.00 11.75 000110 2005-03-06 14:01:01.000 5.00 11.75 000190 2006-10-06 10:01:01.000 10.00 11.75 000110 2006-10-06 10:01:01.000 3.00 11.75 000110 2007-06-06 17:01:01.000 2.00 11.75 000010 2007-04-06 00:00:00.000 4.00 11.75 000010 2007-05-01 00:00:00.000 5.00 11.75 000030 2007-07-06 00:00:00.000 1.00 11.75 000190 2006-10-06 10:01:01.000 10.00 11.75 000030 2004-03-06 14:01:01.000 5.00 11.75 000110 2005-04-06 15:01:01.000 4.00 11.75 000010 2005-05-06 16:01:01.000 3.00 11.75 000190 2006-06-06 17:01:01.000 2.00 11.75 000030 2006-07-06 19:01:01.000 1.00 11.75 000310 2006-03-06 14:01:01.000 5.00 11.75 000310 2006-04-06 15:01:01.000 4.00 11.75 000010 2006-06-06 17:01:01.000 2.00 11.75 000110 2006-07-06 19:01:01.000 1.00 11.75 000190 2005-10-06 10:01:01.000 10.00 11.75 000030 2005-12-06 11:01:01.000 8.00 11.75 000190 2006-01-06 12:01:01.000 7.00 11.75 000030 2006-07-06 19:01:01.000 1.00 11.75 000010 2007-11-06 10:01:01.000 9.00 11.75 000030 2006-07-06 00:00:00.000 1.00 11.75 000190 (39 row(s) affected)
Wrong way of selecting by dates
select sales_date, sales_qty, sales_price, emp_short from sales where sales_date between '2005-10-1' and '2006-02-06'; go
sales_date sales_qty sales_price emp_short ----------------------- --------------------------------------- --------------------------------------- ---------- 2005-10-06 10:01:01.000 10.00 11.75 000310 2005-11-06 10:01:01.000 9.00 11.75 000110 2005-12-06 11:01:01.000 8.00 11.75 000010 2005-11-06 10:01:01.000 9.00 11.75 000010 2006-01-06 12:01:01.000 7.00 11.75 000310 2005-10-06 10:01:01.000 10.00 11.75 000030 2005-12-06 11:01:01.000 8.00 11.75 000190 2006-01-06 12:01:01.000 7.00 11.75 000030 (8 row(s) affected)
We are missing 2 rows.
2006-02-06 13:01:01.000 6.00 11.75 000190 2006-02-06 13:01:01.000 6.00 11.75 000110
Because the date is: 2006-02-06 and the time is: 2006-02-06 13:01:01.000. You asked for between 2005-10-1 0:00:00.000 and 2006-02-06 0:00:00.000, not 2006-02-06 during the day.
Correct way of selecting by dates
select sales_date, sales_qty, sales_price, emp_short from sales where sales_date between '2005-10-1' and '2006-02-06 23:59:59.9'; go
sales_date sales_qty sales_price emp_short ----------------------- --------------------------------------- --------------------------------------- ---------- 2005-10-06 10:01:01.000 10.00 11.75 000310 2005-11-06 10:01:01.000 9.00 11.75 000110 2005-12-06 11:01:01.000 8.00 11.75 000010 2006-02-06 13:01:01.000 6.00 11.75 000190 2005-11-06 10:01:01.000 9.00 11.75 000010 2006-01-06 12:01:01.000 7.00 11.75 000310 2006-02-06 13:01:01.000 6.00 11.75 000110 2005-10-06 10:01:01.000 10.00 11.75 000030 2005-12-06 11:01:01.000 8.00 11.75 000190 2006-01-06 12:01:01.000 7.00 11.75 000030 (10 row(s) affected)
- By inserting the time, you are also selecting the rows for that day.
or
- You can change the way, you write the query:
select sales_date, sales_qty, sales_price, emp_short
from sales
where sales_date >= '2005-10-1' and
sales_date < '2006-02-7';
go
- By setting sales_date, 1 day more and making it a LESS THAN, you will get all the records for the previous day.
or
- You can drop the time from the datetime, and setting all the times to 0:00:00, see: How to drop the time portion of the DateTime in MS SQL Server