Problem
- SQL Server stores both the date and the time in the DateTime.
- SmallDateTime: Stores the minutes in the DateTime field.
- DateTime: Stores the thousand of a second in the DateTime field.
- Often, actually the vast majority of the times, you need to only deal with dates.
Answer
- Drop the time portion of the DateTime
Applies to:
- Micrsosoft SQL Server 2000
- Micrsosoft SQL Server 2005
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 2005-11-06 10:01:01.000 9.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-01-06 12:01:01.000 7.00 11.75 000190 2006-05-06 16:01:01.000 3.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-07-06 19:01:01.000 1.00 11.75 000110 2006-05-06 16: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 2004-02-06 13:01:01.000 6.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)
- SELECT sales_date, sales_qty, sales_price, emp_short
- FROM sales
- WHERE sales_date = '2007-11-06';
- go
Will give:
sales_date sales_qty sales_price emp_short ----------------------- --------------------------------------- --------------------------------------- ---------- (0 row(s) affected)
and will find nothing.
Dropping the time from DateTime
- Actually, it's impossible to actually drop the time portion of a datetime field.
- MS SQL Server does NOT have datatype of DATE.
- You just need to set the time to 0:00
- SELECT sales_date AS 'Date/Time',
- CONVERT(DATETIME,FLOOR(CONVERT(FLOAT, sales_date))) AS 'Date only',
- CONVERT(FLOAT, sales_date) AS 'Date Float',
- FLOOR(CONVERT(FLOAT, sales_date)) AS 'Floor of date',
- sales_qty, sales_price, emp_short
- FROM sales;
Date/Time Date only Date Float Floor of date sales_qty sales_price emp_short ----------------------- ----------------------- ---------------------- ---------------------- --------------------------------------- --------------------------------------- ---------- 2005-10-06 10:01:01.000 2005-10-06 00:00:00.000 38629.4173726852 38629 10.00 11.75 000310 2006-10-06 10:01:01.000 2006-10-06 00:00:00.000 38994.4173726852 38994 10.00 11.75 000310 2005-11-06 10:01:01.000 2005-11-06 00:00:00.000 38660.4173726852 38660 9.00 11.75 000110 2005-11-06 10:01:01.000 2005-11-06 00:00:00.000 38660.4173726852 38660 9.00 11.75 000310 2005-12-06 11:01:01.000 2005-12-06 00:00:00.000 38690.4590393519 38690 8.00 11.75 000010 2006-02-06 13:01:01.000 2006-02-06 00:00:00.000 38752.5423726852 38752 6.00 11.75 000190 2006-03-06 14:01:01.000 2006-03-06 00:00:00.000 38780.5840393519 38780 5.00 11.75 000110 2006-01-06 12:01:01.000 2006-01-06 00:00:00.000 38721.5007060185 38721 7.00 11.75 000190 2006-05-06 16:01:01.000 2006-05-06 00:00:00.000 38841.6673726852 38841 3.00 11.75 000190 2006-04-06 15:01:01.000 2006-04-06 00:00:00.000 38811.6257060185 38811 4.00 11.75 000030 2006-05-06 16:01:01.000 2006-05-06 00:00:00.000 38841.6673726852 38841 3.00 11.75 000010 2004-10-06 10:01:01.000 2004-10-06 00:00:00.000 38264.4173726852 38264 10.00 11.75 000310 2005-11-06 10:01:01.000 2005-11-06 00:00:00.000 38660.4173726852 38660 9.00 11.75 000010 2006-06-06 17:01:01.000 2006-06-06 00:00:00.000 38872.7090393519 38872 2.00 11.75 000190 2006-01-06 12:01:01.000 2006-01-06 00:00:00.000 38721.5007060185 38721 7.00 11.75 000310 2006-02-06 13:01:01.000 2006-02-06 00:00:00.000 38752.5423726852 38752 6.00 11.75 000110 2005-03-06 14:01:01.000 2005-03-06 00:00:00.000 38415.5840393519 38415 5.00 11.75 000190 2006-07-06 19:01:01.000 2006-07-06 00:00:00.000 38902.7923726852 38902 1.00 11.75 000110 2006-05-06 16:01:01.000 2006-05-06 00:00:00.000 38841.6673726852 38841 3.00 11.75 000110 2007-06-06 17:01:01.000 2007-06-06 00:00:00.000 39237.7090393519 39237 2.00 11.75 000010 2007-04-06 00:00:00.000 2007-04-06 00:00:00.000 39176 39176 4.00 11.75 000010 2007-05-01 00:00:00.000 2007-05-01 00:00:00.000 39201 39201 5.00 11.75 000030 2007-07-06 00:00:00.000 2007-07-06 00:00:00.000 39267 39267 1.00 11.75 000190 2004-02-06 13:01:01.000 2004-02-06 00:00:00.000 38021.5423726852 38021 6.00 11.75 000030 2004-03-06 14:01:01.000 2004-03-06 00:00:00.000 38050.5840393519 38050 5.00 11.75 000110 2005-04-06 15:01:01.000 2005-04-06 00:00:00.000 38446.6257060185 38446 4.00 11.75 000010 2005-05-06 16:01:01.000 2005-05-06 00:00:00.000 38476.6673726852 38476 3.00 11.75 000190 2006-06-06 17:01:01.000 2006-06-06 00:00:00.000 38872.7090393519 38872 2.00 11.75 000030 2006-07-06 19:01:01.000 2006-07-06 00:00:00.000 38902.7923726852 38902 1.00 11.75 000310 2006-03-06 14:01:01.000 2006-03-06 00:00:00.000 38780.5840393519 38780 5.00 11.75 000310 2006-04-06 15:01:01.000 2006-04-06 00:00:00.000 38811.6257060185 38811 4.00 11.75 000010 2006-06-06 17:01:01.000 2006-06-06 00:00:00.000 38872.7090393519 38872 2.00 11.75 000110 2006-07-06 19:01:01.000 2006-07-06 00:00:00.000 38902.7923726852 38902 1.00 11.75 000190 2005-10-06 10:01:01.000 2005-10-06 00:00:00.000 38629.4173726852 38629 10.00 11.75 000030 2005-12-06 11:01:01.000 2005-12-06 00:00:00.000 38690.4590393519 38690 8.00 11.75 000190 2006-01-06 12:01:01.000 2006-01-06 00:00:00.000 38721.5007060185 38721 7.00 11.75 000030 2006-07-06 19:01:01.000 2006-07-06 00:00:00.000 38902.7923726852 38902 1.00 11.75 000010 2007-11-06 10:01:01.000 2007-11-06 00:00:00.000 39390.4173726852 39390 9.00 11.75 000030 2006-07-06 00:00:00.000 2006-07-06 00:00:00.000 38902 38902 1.00 11.75 000190 (39 row(s) affected)
- CONVERT(DATETIME,FLOOR(CONVERT(FLOAT, sales_date)))
- Convert the DateTime to a float.
- Drop the decimal portion, the time portion of the DateTime.
- Convert back to a DateTime for date operations.
You can also use:
- CAST(FLOOR(CAST(sales_date AS FLOAT)) AS DATETIME)