How to get number of months

 0 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 more ▼ asked Dec 04 '09 at 06:54 AM in Default INDRA 1 ● 2 ● 2 ● 2 Kristen ♦ 2.2k ● 6 ● 7 ● 10 What are you trying to do? Dec 04 '09 at 07:26 AM Madhivanan I am going to calculate no of months Dec 04 '09 at 10:19 AM INDRA add new comment (comments are locked) 10|1200 characters needed characters left ▼ Viewable by all users

 0 If you want to find out the last day of the date``DECLARE @startDate DATETIMESET @startDate='2007.03.16'SELECT day(dateadd(month,datediff(month,-1,@startdate),-1))`` more ▼ answered Dec 04 '09 at 07:49 AM Madhivanan 1.1k ● 1 ● 3 ● 6 add new comment (comments are locked) 10|1200 characters needed characters left ▼ Viewable by all users

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

By Email:

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1931
x91

asked: Dec 04 '09 at 06:54 AM

Seen: 1224 times

Last Updated: Dec 04 '09 at 08:20 AM