SQLTeam.com | Weblogs | Forums

DateDiff Query


Dear Friends
I have query get The difference between two dates and the result is the month without considering the days when calculating the difference
SELECT '2018-01-01' , '2018-03-31' , DATEDIFF( MONTH , '2018-01-01', '2018-03-31' )
the result is = 2
but the true result is = 3


It works out the difference like the function says, you can alter it by adding/subtracting 1

 datediff(day,'20180101','20180101')as Days
,datediff(day,'20180101','20180102')as Days
,datediff(day,'20180101','20180103')as Days
,datediff(day,'20180101','20180104')as Days
,datediff(day,'20180101','20180105')as Days

 datediff(month,'20180101','20180131')as Months
,datediff(month,'20180101','20180228')as Months
,datediff(month,'20180101','20180331')as Months
,datediff(month,'20180101','20180430')as Months
,datediff(month,'20180101','20180531')as Months

 datediff(day,'20180101','20180101')+1 as Days
,datediff(day,'20180101','20180102')+1 as Days
,datediff(day,'20180101','20180103')+1 as Days
,datediff(day,'20180101','20180104')+1 as Days
,datediff(day,'20180101','20180105')+1 as Days

 datediff(month,'20180101','20180131')+1 as Months
,datediff(month,'20180101','20180228')+1 as Months
,datediff(month,'20180101','20180331')+1 as Months
,datediff(month,'20180101','20180430')+1 as Months
,datediff(month,'20180101','20180531')+1 as Months


query is from '2018-01-01 0:00:00' to '2018-03-31 0:00:00'
is not enought query from '2018-01-01 00:00:00' to '2018-03-31 29:59:59'
so you should add +1 day to ending date