Calculating the number of days between two dates is a trivial matter in T-SQL if you use the DATEDIFF function. However, how many years (or rather, fractions of years) there are between two given dates is a matter of which method (day count convention) you apply. In financial mathematics, a lot of calculations use a 30/360 convention, where you apply certain rules in order to modify each month to contain exactly 30 days.

Why 30/360?

Here’s the problem: If you lend somebody money at 6% interest, you’ll receive 6% for every year that goes by. In practice, you might have agreed that this means monthly interest payments of 0.5%. But if you calculate daily interest payments (or accrued interest), you’d receive 1/365th of 6% in interest for each day, but only 1/366th of 6% if it’s a leap year. Furthermore, a loan from january to june will incur 181 days of interest (182 in a leap year), but the same loan from july to december would incur 184 days of interest.

To remedy some of this complexity, and to make calculations simpler, there are a few different day count conventions that you can agree on when you’re lending somebody money or selling a bond. The list of these conventions is long, so I’ll settle on a common convention known as 30/360. In the 30/360 convention, every month is treated as 30 days, which means that a year has 360 days for the sake of interest calculations. If you want to calculate the interest owed over three months, you can multiply the annual interest by 3 x 30 / 360, which practically enough is 1/4.

The basic 30/360 calculation

The number of days between two dates (@fromDate and @toDate) is:

360*(@y2-@y1) + 30*(@m2-@m1) + (@d2-@d1)

.. where @y2 is the year of the end date, @y1 is the year of the start date, @m2 is the month of the end date, etc.

For this to work, we need to correct @toDay and @fromDay, notably when they are 31, or when the month is february. The rules for how this is done, however, vary a bit between the two most common 30/360 conventions, the US and European ones:

US (NASD) 30/360

Also known as “bond basis” or “30U/360”. Commonly used by US agency issues and corporate bonds.

1. If both the start and end dates are on the last day of february, set @d2 to 30.
2. If the start date is on the last day of february, set @d1 to 30.
3. If @d1 is 30 or 31, and @d2 is 31, set @d2 to 30.
4. If @d1 is 31, set @d1 to 30.

This is the equivalent of the Excel function 360*YEARFRAC(fromDate; toDate; 0).

European 30/360

Also known as “30E/360”.

1. If @d1 is 31, set @d1 to 30.
2. If @d2 is 31, set @d2 to 30.

The Excel equivalent of this is 360*YEARFRAC(fromDate; toDate; 4).

A T-SQL scalar function to calculate 30/360

Based on the two implementations above, I’ve built a scalar function that returns the number of 30/360 days between two dates.

Disclaimer: I’ve made a reasonable effort to validate that this function arrives at the same results as the YEARFRAC function in Excel, but as always, it’s your ass if you put it in your production environment without double-checking it yourself.

CREATE FUNCTION dbo.fn_30_360 (
    @fromDate   date,   --- Start date
    @toDate     date,   --- End date
    @european   bit=0   --- 0=US NASD, 1=European
)
RETURNS int             --- The number of 30/360 days
WITH SCHEMABINDING
AS

BEGIN
    --- Split year, month and day into separate variables:
    DECLARE @y1 smallint=DATEPART(yy, @fromDate), @y2 smallint=DATEPART(yy, @toDate),
            @m1 smallint=DATEPART(mm, @fromDate), @m2 smallint=DATEPART(mm, @toDate),
            @d1 smallint=DATEPART(dd, @fromDate), @d2 smallint=DATEPART(dd, @toDate);

    --- US: If both from and to dates are last day of february, set @d2 to 30.
    IF (@european=0 AND
        @m1=2 AND DATEPART(dd, DATEADD(dd, 1, @fromDate))=1 AND
        @m2=2 AND DATEPART(dd, DATEADD(dd, 1, @toDate))=1)
        SET @d2=30;

    --- US: If from date is last of february, set @d1=30.
    IF (@european=0 AND @m1=2 AND DATEPART(dd, DATEADD(dd, 1, @fromDate))=1)
        SET @d1=30;

    --- US: If @d1 is 30 or 31 and @d2 is 31, set @d2 to 30
    ---     If @d1 is 31, set @d1 to 30.
    IF (@european=0 AND @d2>30 AND @d1>=30) SET @d2=30;
    IF (@european=0 AND @d1>30)             SET @d1=30;

    --- European: Starting and ending dates on the 31st become the 30th.
    IF (@european=1 AND @d1=31) SET @d1=30;
    IF (@european=1 AND @d2=31) SET @d2=30;

    --- Add it all together and return:
    RETURN 360*(@y2-@y1) + 30*(@m2-@m1) + (@d2-@d1);
END;

I’ve found most of my information for this post in the Wikipedia article on Day count conventions. If you’re really into this stuff, or if you need more information, do check it out.

Until next week, stay tuned!