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

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

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.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