How do you extract just date from datetime in T-Sql?

Question

I am running a select against a datetime column in SQL Server 2005. I can select only the date from this datetime column?

Answer 1

Best way is:

   SELECT DATEADD(day, DATEDIFF(Day, 0, @ADate), 0) 

This is because internally, SQL Server stores all dates as two integers, of which the first one is the ****number of days*** since 1 Jan 1900. (the second one is the time portion, stored as the number of seconds since Midnight. (seconds for SmallDateTimes, or milleseconds for DateTimes)
Using the above expression is better because it avoids all conversions, directly reading and accessing that first integer in a dates internal representation without having to perform any processing... the two zeroes in the above expression (which represent 1 Jan 1900), are also directly utilized w/o processing or conversion, because they match the SQL server internal representation of the date 1 jan 1900 exactly as presented (as an integer)..

*NOTE. Actually, the number of date boundaries (midnights) you have to cross to get from the one date to the other.

Answer 2

Yes, by using the convert function. For example:

select getdate(), convert(varchar(10),getdate(),120)

RESULTS:

----------------------- ----------
2010-05-21 13:43:23.117 2010-05-21