How to get only date part while using dateadd() and getdate()

Question

I want to display records of last 4 months from current date.

I don't want to consider time

How can I get just date part from the below query?

where OrderDate >= DATEADD(month, -4, GETDATE())

Potential duplicate of http://stackoverflow.com/questions/1427469/compare-dates-in-t-sql-ignoring-the-time-part — Martin, Feb 25 '14 at 10:05

Darren · Answer 1 · 2014-02-25T10:21:14.483

5

If you're using SQL Server 2008, try converting GETDATE() to a DATE directly.

WHERE OrderDate >= DATEADD(month, -4, CONVERT(date, GETDATE()))

http://sqlfiddle.com/#!3/df444/2

edited Feb 25 '14 at 10:21

answered Feb 25 '14 at 10:05

Darren

68,902
24
138
144

I can agree with your first, but why would you perform a conversion to varchar specifying a format that can be interpreted ambiguously and then let the system perform an implicit conversion back to `datetime`, where such ambiguity may produce incorrect results or errors? – Damien_The_Unbeliever Feb 25 '14 at 10:20
@Damien_The_Unbeliever - fair assumption. I have removed the latter. – Darren Feb 25 '14 at 10:21
Still getting date time as output...2013-10-25 15:52:31.000 – Challa Jyothi Feb 25 '14 at 10:39

score 0 · Accepted Answer · answered Feb 25 '14 at 10:16

0

Why not use the simple DATEDIFF function

where DATEDIFF(MM, OrderDate, GETDATE()) < 4

answered Feb 25 '14 at 10:16

Hitesh

3,449
8
39
57

1

Using a function over a column (ie `OrderDate`) make it impossible for SQL Server to use existing indexes. – Gerardo Lima Feb 25 '14 at 10:36

score 0 · Answer 3 · answered Feb 25 '14 at 10:42

If you can't use the DATE type, there's the old way: convert the DATETIME value to CHAR, trim the hour components and then convert it back to DATETIME, so the hour components will be zeroed:

SELECT CONVERT(DATETIME, CONVERT(CHAR(8), GETDATE(), 112), 112)
-- -----------------------
-- 2014-02-25 00:00:00.000

The important thing is to use the function over the scalar parameter (and not on the column) to allow the usage of existing indexes.

How to get only date part while using dateadd() and getdate()

3 Answers3