CODEWITHSUNDEEP
sqlsql-serverperformancet-sqlssms
kuml
kuml

Reputation: 251

Why DateAdd() and DATEDIFF() gives different result and which is better in performance?

Currently, I am trying to run my sql query in 2 different ways.

WHERE order_date_time >= DateAdd(month,-3,getdate())

and

WHERE DATEDIFF(month,order_date_time,GetDate()) <= 3

1) Why I am getting 2 different results? Shouldn't I be getting same value as I am trying to find records from last 3 months or less?

Am I not calculating the 3 months difference correctly in my where clause?

2) Which function is better and return results faster in terms of performance?

Upvotes: 4

Views: 3494

Answers (2)

Thom A
Thom A

Reputation: 95554

DATEDIFF counts the number of "ticks" between 2 datetimes. So, for example DATEDIFF(MONTH,'2019-01-31T23:59:59','2019-02-01T00:00:00') returns 1, even though only 1 second has passed; the month has changed (so one "tick" has occured).

For the perspective of what is "better", that depends on your requirement, however, WHERE DATEDIFF(MONTH,order_date_time,GETDATE()) <= 3 is not advisable here, as it's non-SARGable. This is because order_date_time is contained within the function DATEDIFF.

I would use WHERE order_date_time >= DATEADD(MONTH,-3,GETDATE()) (assuming you want rows where order_date_time is in the last 3 months).

Upvotes: 7

Gordon Linoff
Gordon Linoff

Reputation: 1269603

These are your conditions:

WHERE order_date_time >= DateAdd(month, -3, getdate())
WHERE DATEDIFF(month, order_date_time, GetDate()) <= 3

The most important thing . . . These do different things. The first goes back three months. So, if today is April 15th, then it takes everything after Jan 15th.

The second looks at month boundaries. So, it counts all of the January the same way and takes all values since Jan 1.

Although you should use the version you need, the first version is much, much better. Basically, it can make use of indexes and partitions. The function call DATEDIFF() makes it hard (impossible?) for the optimizer to use indexes on order_date_time.

If you intend the second form, I would recommend rephrasing it as something like this:

WHERE order_date_time >= datefromparts(year(dateAdd(month, -3, getdate())), month(dateAdd(month, -3, getdate())), 1)

Upvotes: 4

Related Questions