YearFrac in Sql 2005

Question

How do I write the YearFrac function that comes with Excel in Sql 2005?

Answer 1

Find out the distance between two dates using DateDiff, and then divide that value by 365

EDIT

You could, of course, create your own function to do the job:

create function yearfrac (@d1 datetime, @d2 datetime) returns float
as
begin
return abs(datediff(d, @d1, @d2)) / 365.00
end

Answer 2

For the default usage (two data parameters):

datediff(day, date1, date2) / 360.0

For the special case where the third parameter has the value 3:

datediff(day, date1, date2) / 365.0

Edit:
Added a decimal to make it a floating point operation.