# question

asked

## How to get number of months

I wrote a code for getting number of months.The code is below can you check

``````
DECLARE @startDate DATETIME,
@endDate  DATETIME;
SET @startDate='2007.03.16';
SET @endDate='2009.03.28';
DECLARE @wholepart  FLOAT;
DECLARE @fraction FLOAT;
IF(MONTH(@startDate)=MONTH(@endDate))
BEGIN
IF (MONTH(@endDate)IN(1,3,5,7,8,10,12))
BEGIN
SELECT  @wholepart =DATEDIFF(MONTH,@startDate,@endDate) *1.0,
@fraction  =DATEDIFF(DAY,@startDate,DATEADD(DAY,1,@endDate)) *1.0/31.0
SELECT ROUND(@wholepart+@fraction,0) AS '31DAYS'
END
IF(MONTH(@endDate)IN(4,6,9,11))
IF (DAY(@startDate)=16)
BEGIN
SELECT   @wholepart =DATEDIFF(MONTH,@startDate,@endDate) *1.0,
@fraction  =DATEDIFF(DAY,@startDate,DATEADD(DAY,1,@endDate)) *1.0/30.0
SELECT ROUND(@wholepart+@fraction,0) AS '30DAYS'
END
ELSE
BEGIN
SELECT   @wholepart =DATEDIFF(MONTH,@startDate,@endDate) *1.0,
@fraction =DATEDIFF(DAY,DATEADD(MONTH,@wholepart,@startDate),@endDate) *1.0/30.0
SELECT ROUND(@wholepart+@fraction,0) AS '30DAYS'
END
IF(MONTH(@startDate)=2)
IF (DAY(@startDate)=15)
BEGIN
IF(YEAR(@startDate)%4=0)
SELECT   @wholepart =DATEDIFF(MONTH,@startDate,@endDate) *1.0,
@fraction = DATEDIFF(DAY,DATEADD(MONTH,@wholepart,@startDate),DATEADD(DAY,1,@endDate)) *1.0/29.0
ELSE
SELECT   @wholepart =DATEDIFF(MONTH,@startDate,@endDate) *1.0,
@fraction = DATEDIFF(DAY,DATEADD(MONTH,@wholepart,@startDate),DATEADD(DAY,1,@endDate)) *1.0/28.0
SELECT ROUND(@wholepart+@fraction,0) AS '28DAYS'
END
ELSE
BEGIN
SELECT   @wholepart =DATEDIFF(MONTH,@startDate,@endDate) *1.0,
@fraction =DATEDIFF(DAY,DATEADD(MONTH,@wholepart,@startDate),@endDate) *1.0/28.0
SELECT ROUND(@wholepart+@fraction,0) AS '28DAYS'
END
END

IF(MONTH(@startDate)!=MONTH(@endDate))
BEGIN
SELECT   @wholepart =DATEDIFF(MONTH,@startDate,@endDate) *1.0,
@fraction =DATEDIFF(DAY,DATEADD(MONTH,@wholepart,@startDate),@endDate) *1.0/
CASE WHEN MONTH(@endDate) IN (1,3,5,7,8,10,12) THEN 31.0
WHEN MONTH(@endDate) IN (4,6,9,11) THEN 30.0
WHEN MONTH(@endDate)=2  AND (YEAR(@endDate)%4 = 0) THEN 29.0
ELSE 28.0 END

SELECT ROUND(@wholepart+@fraction,0) AS 'NORMAL'
END
``````

i am getting no of months as 48 can you fix this and send me why it coming

2 comments

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

What are you trying to do?
0 Likes 0 ·
I am going to calculate no of months
0 Likes 0 ·

### 1 Answer

·
answered

If you want to find out the last day of the date

``````DECLARE @startDate DATETIME
SET @startDate='2007.03.16'
SELECT day(dateadd(month,datediff(month,-1,@startdate),-1))
``````

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

### Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.