How to return only the Date from a SQL Server Date

Question

SELECT GETDATE()

Returns: 2008-09-22 15:24:13.790

I want that date part without the time part: 2008-09-22 00:00:00.000

How can I get that?

Answer 1

You can use following for date part and formatting the date:

DATENAME => Returns a character string that represents the specified datepart of the specified date

DATEADD => The DATEPART() function is used to return a single part of a date/time, such as year, month, day, hour, minute, etc.

DATEPART =>Returns an integer that represents the specified datepart of the specified date.

CONVERT() = > The CONVERT() function is a general function that converts an expression of one data type to another. The CONVERT() function can be used to display date/time data in different formats.

Answer 2

Date(date&time field) and DATE_FORMAT(date&time,'%Y-%m-%d') both returns only date from date&time

Answer 3

DATEADD and DATEDIFF are better than CONVERTing to varchar. Both queries have the same execution plan, but execution plans are primarly 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 becauses 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 rowset 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.

Answer 4

Using FLOOR() - just cut time part.

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

Answer 5

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

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

I holpe it is useful.

Answer 6

If you need result in varchar datatype you should go through

SELECT CONVERT(DATE, GETDATE()) --2014-03-26
SELECT CONVERT(VARCHAR(10), GETDATE(), 111) --2014/03/26

which is already mentioned above

If you need result in date and time format you should go through any of the below query

1) SELECT CONVERT(DATETIME,CONVERT(VARCHAR(10), GETDATE(), 111)) as OnlyDate --2014-03-26 00:00:00.000

2) SELECT CONVERT(DATETIME,CONVERT(VARCHAR(10), GETDATE(), 112)) as OnlyDate --2014-03-26 00:00:00.000

3)

 DECLARE  @OnlyDate DATETIME
   SET @OnlyDate = DATEDIFF(DD, 0, GETDATE())
   SELECT @OnlyDate AS OnlyDate

--2014-03-26 00:00:00.000