How to search for dates with SQL Server
- Microsoft SQL Server does NOT support DATE as a data type.
- 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.
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)
Selecting by the date
select sales_date,sales_qty,sales_price,emp_short from sales where sales_date >= '2007-01-01'; go
sales_date sales_qty sales_price emp_short ----------------------- --------------------------------------- --------------------------------------- ---------- 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 2007-11-06 10:01:01.000 9.00 11.75 000030 (5 row(s) affected)
- The date is better entered as 'YYYY-MM-DD' .
- The date is surrounded by single quotes ' .
- The format of the date can be changed by: SET DATEFORMAT
- The setting of SET DATEFORMAT is set at execute or run time and not at parse time.
SET DATEFORMAT mdy;
or
SET DATEFORMAT ymd;