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

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())


If using SQL 2008 and above:

select cast(getdate() as date)