SQLTeam.com | Weblogs | Forums

Exact DateDiff


#1

I has the query below:

SELECT DATEDIFF(MONTH, '2017-04-19', '2017-11-01') 

It return to me 7 months. How can I change my query to make it show 7 months only when the date is '2017-11-19'? I need the exact date difference.


#2
with cte(dt1,dt2)
  as (select *
        from (values(cast('2017-04-19' as date),cast('2017-11-01' as date))
                   ,(cast('2017-04-19' as date),cast('2017-11-19' as date))
                   ,(cast('2017-04-19' as date),cast('2017-12-18' as date))
             ) as cte(dt1,dt2)
     )
select dt1
      ,dt2
      ,datediff(month,dt1,dt2)
      -case when datepart(day,dt1)<=datepart(day,dt2) then 0 else 1 end
       as months
  from cte
;