How to return only the Date from a SQL Server DateTime datatype
I want that date part without the time part:
How can I get that?
SQL Server 2008 and higher, you should
CONVERT to date:
SELECT CONVERT(date, getdate())
On older versions, you can do the following:
SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, @your_date))
SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))
- No need to think about
As suggested by Michael
Use this variant:
SELECT DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0)
select getdate() SELECT DATEADD(hh, DATEDIFF(hh, 0, getdate()), 0) SELECT DATEADD(hh, 0, DATEDIFF(hh, 0, getdate())) SELECT DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0) SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, getdate())) SELECT DATEADD(mm, DATEDIFF(mm, 0, getdate()), 0) SELECT DATEADD(mm, 0, DATEDIFF(mm, 0, getdate())) SELECT DATEADD(yy, DATEDIFF(yy, 0, getdate()), 0) SELECT DATEADD(yy, 0, DATEDIFF(yy, 0, getdate()))
2019-04-19 08:09:35.557 2019-04-19 08:00:00.000 4763-02-17 00:00:00.000 2019-04-19 00:00:00.000 2019-04-19 00:00:00.000 2019-04-01 00:00:00.000 1903-12-03 00:00:00.000 2019-01-01 00:00:00.000 1900-04-30 00:00:00.000
Read more... Read less...
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 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.
SELECT CONVERT(datetime, CONVERT(varchar, GETDATE(), 101))