Calculate Decimal Number of Months between two Dates

[akabatman2]

Hello all,

I would like to be able to find the difference between two dates with the resulting value being the number of months, between the @start and @end Date. The resulting value would be a decimal value for use in calculating the average number of months for a contract term for instance.

Record#1:
@startdate = '1/1/2005'
@enddate = '1/15/2005'

ContractTermMonths = .5

Record#2:

@startdate = '2/1/2005'
@enddate = '4/30/2005'

ContractTermMonths = 3

I was thinking this might be a UDF that would need @DatePart(To determine if the result would be the decimal value of Months or Years or Days), @StartDate,EndDate. This way the table.startdate and table.enddate fields could be returned in a select statement against a SQL Server table and the average aggregated.

I wish I knew how to code this, I am thinking I would have to be at the DBA level to figure this one out.

Any ideas on a solution?

Any help would be much appreciated.

 

[mercwrought]

here is an idea but because of the days in a month it is not real accurate

select round(cast(datediff(d,@startdate,@enddate)as decimal(13,2))/29,1)

 

[akabatman2]

Hi mercwrought,
Thank you for this guestimate, I will test it out.

<div class="body">select round(cast(datediff(d,@startdate,@enddate)as decimal(13,2))/29,1)</div>

What I am really looking to do is get something that is accurate, a User Defined Function Maybe?

Anybody else have any ideas?

Thank you very much.

 

[akabatman2]

Mercwrought,

On a side note; I tried to replicate your nicely formated code snippet. How did you do that? I'd like to learn how.

Thanks.

 

[checkai]

put

[ code ]
'code goes here
[/ code ]

but with no spaces

"...we both know I'm training to become a cagefighter...see what happens if you try 'n hit me..."