How do you extract just date from datetime in T-Sq

问题:

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

回答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.

回答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

回答3:

You can use the functions:

1. day(date)
2. month(date)
3. year(date)

回答4:

Also the Datepart() function might be of some use:

http://msdn.microsoft.com/en-us/library/ms174420(SQL.90).aspx

回答5:

DECLARE @dToday DATETIME
SET @dToday = CONVERT(nvarchar(20), GETDATE(), 101)
SELECT @dToday AS Today

This returns today's date at 12:00am : '2010-05-21 00:00:00.000' Then you can use the @dToday variable in a query as needed