I recently had a colleague stop by asking how he could eliminate the time portion of a date when he extracted the data from a SQL table to MS-Excel. He was having trouble grouping dates in Excel because the data contained the time part as well. My first thought was using DATEPART but my wonderful DBA team member Mary T. suggested using the CONVERT function with the style 101. Worked out perfectly. Here is a sample code displaying the results. There are other styles as well with the CONVERT function.
SELECT [OrderDate], CONVERT(varchar(10), [OrderDate], 101) AS JustOrderDate FROM [Northwind].[dbo].[Orders]
1996-07-04 00:00:00.000 07/04/1996
1996-07-05 00:00:00.000 07/05/1996