How to return only the Date from a SQL Server DateTime datatype

SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, @your_date))

for example

SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))

gives me

2008-09-22 00:00:00.000

Pros:

• No varchar<->datetime conversions required
• No need to think about locale

SQLServer 2008 now has a 'date' data type which contains only a date with no time component. Anyone using SQLServer 2008 and beyond can do the following:

SELECT CONVERT(date, GETDATE())

If using SQL 2008 and above:

select cast(getdate() as date)

DATEADD and DATEDIFF are better than CONVERTing to varchar. Both queries have the same execution plan, but execution plans are primarily about data access strategies and do not always reveal implicit costs involved in the CPU time taken to perform all the pieces. If both queries are run against a table with millions of rows, the CPU time using DateDiff can be close to 1/3rd of the Convert CPU time!

To see execution plans for queries:

set showplan_text on
GO 

Both DATEADD and DATEDIFF will execute a CONVERT_IMPLICIT.

Although the CONVERT solution is simpler and easier to read for some, it is slower. There is no need to cast back to DateTime (this is implicitly done by the server). There is also no real need in the DateDiff method for DateAdd afterward as the integer result will also be implicitly converted back to DateTime.

SELECT CONVERT(varchar, MyDate, 101) FROM DatesTable

  |--Compute Scalar(DEFINE:([Expr1004]=CONVERT(varchar(30),[TEST].[dbo].[DatesTable].[MyDate],101)))
       |--Table Scan(OBJECT:([TEST].[dbo].[DatesTable]))

SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, MyDate)) FROM DatesTable

  |--Compute Scalar(DEFINE:([Expr1004]=dateadd(day,(0),CONVERT_IMPLICIT(datetime,datediff(day,'1900-01-01 00:00:00.000',CONVERT_IMPLICIT(datetime,[TEST].[dbo].[DatesTable].[MyDate],0)),0))))
       |--Table Scan(OBJECT:([TEST].[dbo].[DatesTable]))

Using FLOOR() as @digi suggested has performance closer to DateDiff, but is not recommended as casting the DateTime data type to float and back does not always yield the original value.

Remember guys: Don't believe anyone. Look at the performance statistics, and test it yourself!

Be careful when you're testing your results. Selecting many rows to the client will hide the performance difference because it takes longer to send the rows over the network than it does to perform the calculations. So make sure that the work for all the rows is done by the server but there is no row set sent to the client.

There seems to be confusion for some people about when cache optimization affects queries. Running two queries in the same batch or in separate batches has no effect on caching. So you can either expire the cache manually or simply run the queries back and forth multiple times. Any optimization for query #2 would also affect any subsequent queries, so throw out execution #1 if you like.

Here is full test script and performance results that prove DateDiff is substantially faster than converting to varchar.

Try this:

SELECT CONVERT(VARCHAR(10),GETDATE(),111)

The above statement converts your current format to YYYY/MM/DD, please refer to this link to choose your preferable format.

SELECT CONVERT(datetime, CONVERT(varchar, GETDATE(), 101))

You can use the CONVERT function to return only the date. See the link(s) below:

Date and Time Manipulation in SQL Server 2000

CAST and CONVERT

The syntax for using the convert function is:

CONVERT ( data_type [ ( length ) ] , expression [ , style ] ) 

Just do:

SELECT CAST(date_variable AS date)

or with with PostgreSQL:

SELECT date_variable::date

This is called typecasting btw!

Using FLOOR() - just cut time part.

SELECT CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME)

To obtain the result indicated, I use the following command.

SELECT CONVERT(DATETIME,CONVERT(DATE,GETDATE()))

I holpe it is useful.

SELECT DATEADD(DD, DATEDIFF(DD, 0, GETDATE()), 0)

SELECT DATEADD(DAY, 0, DATEDIFF(DAY,0, GETDATE()))

SELECT CONVERT(DATETIME, CONVERT(VARCHAR(10), GETDATE(), 101))

Edit: The first two methods are essentially the same, and out perform the convert to varchar method.

I think this would work in your case:

CONVERT(VARCHAR(10),Person.DateOfBirth,111) AS BirthDate
//here date is obtained as 1990/09/25

In this case, date only, you we are gonna run this query:

SELECT CONVERT(VARCHAR(10), getdate(), 111);

Syntax:

SELECT CONVERT (data_type(length)),Date, DateFormatCode)

Ex:

Select CONVERT(varchar,GETDATE(),1) as [MM/DD/YY]
Select CONVERT(varchar,GETDATE(),2) as [YY.MM.DD]

all dateformatcodes about Date:

DateFormatCode  Format
1       [MM/DD/YY]
2       [YY.MM.DD]
3       [DD/MM/YY]
4       [DD.MM.YY]
5       [DD-MM-YY]
6       [DD MMM YY]
7       [MMM DD,YY]
10      [MM-DD-YY]
11      [YY/MM/DD]
12      [YYMMDD]
23      [yyyy-mm-dd]
101     [MM/DD/YYYY]
102     [YYYY.MM.DD]
103     [DD/MM/YYYY]
104     [DD/MM/YYYY]
105     [DD/MM/YYYY]
106     [DD MMM YYYY]
107     [MMM DD,YYYY]
110     [MM-DD-YYYY]
111     [YYYY/MM/DD]
112     [YYYYMMDD]

My common approach to get date without the time part..

 SELECT CONVERT(VARCHAR(MAX),GETDATE(),103)

 SELECT CAST(GETDATE() AS DATE)

If you want the date to show 2008-09-22 00:00:00.000

then you can round it using

SELECT CONVERT(datetime, (ROUND(convert(float, getdate()-.5),0)))

This will show the date in the format in the question

My Style

      select Convert(smalldatetime,Convert(int,Convert(float,getdate())))

select cast(createddate as date) as derivedate from table 

createdate is your datetime column , this works for sqlserver

you can use like below for different different type of output for date only

1. SELECT CONVERT(datetime, CONVERT(varchar, GETDATE(), 103)) -----dd/mm/yyyy

2. SELECT CONVERT(datetime, CONVERT(varchar, GETDATE(), 101))------mm/dd/yyyy

3. SELECT CONVERT(datetime, CONVERT(varchar, GETDATE(), 102))

Wow, let me count the ways you can do this. (no pun intended)

In order to get the results you want in this format specifically:

2008-09-22

Here are a few options.

SELECT CAST(GETDATE() AS DATE) AS 'Date1'
SELECT Date2  = CONVERT(DATE, GETDATE())
SELECT CONVERT(DATE, GETDATE()) AS 'Date3'
SELECT CONVERT(CHAR(10), GETDATE(), 121) AS 'Date4'
SELECT CONVERT(CHAR(10), GETDATE(), 126) AS 'Date5'
SELECT CONVERT(CHAR(10), GETDATE(), 127) AS 'Date6'

So, I would suggest picking one you are comfortable with and using that method across the board in all your tables.

All these options return the date in the exact same format. Why does SQL Server have such redundancy?

I have no idea, but they do. Maybe somebody smarter than me can answer that question.

Hope this helps someone.

Starting from SQL Server 2022 (16.x), another option is DATETRUNC() function using day as value of datepart parameter:

SELECT DATETRUNC(day, GETDATE());

select convert(getdate() as date)

select CONVERT(datetime,CONVERT(date, getdate()))

As there has been many changes since this question had answers, I wanted to provide a new way to get the requested result. There are two ways to parse DATETIME data. First, to get the date as this question asks:

DATEVALUE([TableColumnName])

Second, to get the time from the value:

TIMEVALUE([TableColumnName])

Example:

Table: Customers

Column: CreationDate as DateTime

[Customers].[CreationDate]: 2/7/2020 09:50:00

DATEVALUE([Customers].[CreationDate]) '--> Output: 2/7/2020
TIMEVALUE([Customers].[CreationDate]) '--> Output: 09:50:00

I hope that this helps as I was searching for a while and found many answers as seen in this question and none of those worked. IE CAST and CONVERT.

Happy Coding!

The easiest way would be to use: SELECT DATE(GETDATE())