Problem in getting months in ceiling or floor in SQL Server

In SQL Server, I am trying to get number of months between two dates.

I want the solution like this:

i have tried below functions but desired output is not coming: DATEDIFF(dd,OPENDATE,MATURITYDATE)/30 , DATEDIFF(mm,OPENDATE,MATURITYDATE)

– P B
Sep 15 '18 at 7:58

There are many different days in a month like Jun 31 Feb 28 or 29. What's your logic to get days of month numbe?

– D-Shih
Sep 15 '18 at 8:10

i am simply assuming days of month to be 30 days. my query is that if there are 135 days then the number of months are coming 4.5 months. i want it to be 5 months(ceiling of 4.5, or in other case floor if months are 4.2 )

– P B
Sep 15 '18 at 8:18

Ok I got it, you can try my answer:)

– D-Shih
Sep 15 '18 at 8:24

For example, if the dates are 2018-01-01 and 2018-01-16. That's 15 days, so you would want 0.5 rounded up to 1 in this case. Another way to look at it is that 15 days of passed in a month of 31 days, so that's 15/31 months, which would round down to 0.

– David Dubois
Sep 16 '18 at 3:14

1 Answer
1

If you are assuming days of month to be 30 days.

DATEDIFF return integer difference days.

DATEDIFF

integer

So, you can try to use CAST(days as decimal) let the day number be float number, then do ROUND get your result.

CAST(days as decimal)

ROUND

select ROUND(CAST(DATEDIFF(dd,OPENDATE,MATURITYDATE)as decimal) /30,0 ) from T

sqlfiddle

Thanks for contributing an answer to Stack Overflow!

But avoid …

To learn more, see our tips on writing great answers.

Required, but never shown

By clicking "Post Your Answer", you agree to our terms of service, privacy policy and cookie policy

搜尋此網誌

Dfyjkt