How to return only the Date from a SQL Server DateTime datatype
On 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))
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
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()))
Output:
2019-04-19 08:09:35.5572019-04-19 08:00:00.0004763-02-17 00:00:00.0002019-04-19 00:00:00.0002019-04-19 00:00:00.0002019-04-01 00:00:00.0001903-12-03 00:00:00.0002019-01-01 00:00:00.0001900-04-30 00:00:00.000
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())