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

Question

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?

Answer 1

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