Select a date without time (SQL Server (+)

Question

I would like to retrieve a date minus 1 year, but without time notation.

The following query

SELECT DATEADD(year, -1, GETDATE())

Output:

2015-03-30 10:48:04.220

What I want is 2015-03-30 00:00:00:000

Similar to:

(DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0))

Which results in: 2016-03-30 00:00:00.000

What is the correct or easiest way to do this?

"What I want is `2015-03-30 00:00:00:000`". Strange, but it looks like that includes a fairly precise representation of midnight. Do you want the time-of-day or just a [`date`](https://msdn.microsoft.com/en-us/library/bb630352.aspx)? — HABO, Mar 30 '16 at 13:17

score 1 · Answer 1 · edited Oct 10 '17 at 10:40

1

On SQL Server 2008 and higher, you should convert to date:

SELECT CONVERT(date, (DATEADD(year, -1, GETDATE())))

On older versions, you can do the following:

SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, DATEADD(year, -1, GETDATE())))

edited Oct 10 '17 at 10:40

Michael Dodd

answered Mar 30 '16 at 09:03

Mithrandir

1 Answers1