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