Peter Larsson Blog

Today I am going to show you one way to calculate a decimal year. This is not a easy as it sounds because some years (leap years) include a leap day so the number of days in a year is not consistent.
The longest defined consistent time period there is, is a week. A week is seven days or 168 hours. However, a week can be defined to have different start weekday in different regions of the world. Enough said about that.

First, set up a test environment by declaring and setting two datetime variables like this

declare @f datetime
       ,@t datetime

select @f = '03/01/2004', @t = '02/28/2012'

What we need now is an algorithm that for every day in a leap year is divided by 366 days and for every day in a non-leap year is divided by 365.
Like this

SELECT  CAST(SUM(
        CASE
            WHEN y = 0 THEN 1.0E * (t - f) / d
            WHEN Number = 0 THEN 1.0E * (d - f + 1) / d
            WHEN Number = y THEN 1.0E * t / d
            ELSE 1.0E
        END) AS DECIMAL(10, 2))
FROM    (
            SELECT  Number,
                    DATEDIFF(YEAR, @f, @t) AS y,
                    CASE ISDATE(10000 * Number + 10000 * DATEPART(YEAR, @f) + 229)
                        WHEN 0 THEN 365
                        ELSE 366
                    END AS d,
                    DATEPART(DAYOFYEAR, @f) AS f,
                    DATEPART(DAYOFYEAR, @t) AS t
            FROM    master..spt_values
            WHERE   Type = 'P'
                    AND Number <= DATEDIFF(YEAR, @f, @t)
        ) AS d

In my example above, I am using the master..spt_values table which for SQL Server 2005 and later has 2048 records (2048 years) and for SQL Server 2000 and earlier has 256 records (256 years). You can easily replace the master..spt_values table with a number tally table of your own. Just make sure it is zero-based or you will have to edit the suggestion above.

Here is a link how DATEDIFF works
http://www.sqlteam.com/article/datediff-function-demystified