What is the main difference between DateAdd() and DATEDIFF() and which is faster?

Transact-SQL
1
  1. 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

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?

  1. In terms of performance of the sql query, which function should I be using? DateAdd() or DATEDIFF(), which one is faster?
2

hi

i am trying to explain ..
:slight_smile:
:slight_smile:

please see below ... is it clear ???

drop create data .. 
drop table #abc 
go 

create table #abc 
(
order_date_time datetime
)
go 

insert into #abc select '2019-03-01'
insert into #abc select '2019-02-01'
insert into #abc select '2019-01-01'
go
SQL... 
SELECT Getdate()                     AS getdate1, 
       Dateadd(month, -3, Getdate()) AS ok, 
       order_date_time 
FROM   #abc 

go 

SELECT Getdate()                     AS getdate1, 
       Dateadd(month, -3, Getdate()) AS ok, 
       order_date_time 
FROM   #abc 
WHERE  order_date_time >= Dateadd(month, -3, Getdate()) 

go 

SELECT Getdate()                                   AS getdate1, 
       Datediff(month, order_date_time, Getdate()) AS ok, 
       order_date_time 
FROM   #abc 

go 

SELECT Getdate()                                   AS getdate1, 
       Datediff(month, order_date_time, Getdate()) AS ok, 
       order_date_time 
FROM   #abc 
WHERE  Datediff(month, order_date_time, Getdate()) <= 3 

go

image
image.png1712×1145 21.6 KB

3

hi

one query takes datetime when comparing

another takes only the month when comparing

:slight_smile:

4

hi

this is what i mean !!!!!

image
image.png2077×803 15.6 KB

5

Hi Harishgg1

Thank you for the explanation and examples.

I get it now, DateAdd(month, -3, getdate()) will be always calculative relative cycle of exact 3 months from today's day. This is exactly what I need as I need to be able to run this query next month so calculation of 3 months from that date should be calculated while i see how datediff is calculating months (irrespective of today's date).

In my case, I will go with DateAdd() since I need to be able to calculate 3 months from the day when I run query.