CODEWITHSUNDEEP
sql-server-2005t-sql
Tyson Nero
Tyson Nero

Reputation: 2078

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

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

Upvotes: 8

Views: 19342

Answers (6)

Meikel
Meikel

Reputation: 324

CONVERT (date, GETUTCDATE())
CONVERT (date, GETDATE())
CONVERT (date, '2022-18-01')

I don't know why the others recommend it with varchar(x) tbh.

https://learn.microsoft.com/de-de/sql/t-sql/functions/getdate-transact-sql

Upvotes: 1

Charles Bretana
Charles Bretana

Reputation: 146499

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.

Upvotes: 8

Noah
Noah

Reputation: 15320

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

Upvotes: 7

Ed B
Ed B

Reputation: 6054

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

Upvotes: 0

Ryan
Ryan

Reputation: 698

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

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

Upvotes: 1

brickner
brickner

Reputation: 6585

You can use the functions:

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

Upvotes: 1

Related Questions