CODEWITHSUNDEEP
sql-server
Srini
Srini

Reputation: 23

Finding Months Difference in Days

I want to find the difference between two dates which should be exact in months like if the date difference is greater than 182 days them on 183rd Day it should show as 7 Months.I tried below one,

SELECT ROUND(cast(DATEDIFF(DD,'2018-01-01 18:45:30.203',GETDATE()) as float)/30,0)

but it has 15 days difference.

Upvotes: 0

Views: 39

Answers (2)

Wolfgang Kais
Wolfgang Kais

Reputation: 4100

I think that calculating the difference in months as an integer is very similar to calculating the age of a person. We can take the DATEDIFF in months, add this number of months to the first date and compare that to the second date to decide whether we have to subtract 1 from the difference:

DECLARE @Date1 datetime = '2018-01-01 18:45:30.203';
DECLARE @Date2 datetime = GETDATE();

SELECT
  CASE 
    WHEN DATEADD(month, DATEDIFF(month, @Date1, @Date2), @Date1) > @Date2
    THEN DATEDIFF(month, @Date1, @Date2) - 1
    ELSE DATEDIFF(month, @Date1, @Date2)
  END

Upvotes: 0

S3S
S3S

Reputation: 25112

I wouldn’t use 30. It’s fail on some months. For example Jan 1 and March 2 since February doesn’t have at least 30 days. But I think this is what you are after. If the current day isn’t the first of the month then add a month. 

SELECT 
   Case 
        when datepart(day,getdate()) > 1
        Then datediff(month,'2018-01-01 18:45:30.203',GETDATE()) + 1
        Else datediff(month,'2018-01-01 18:45:30.203',GETDATE())
    End

Upvotes: 1

Related Questions