Convert a date range to an interval description

This answer shows an implementation using a SQL Server (2005+) CLR function.

-- Enable CLR (if necessary)
EXECUTE sys.sp_configure 
    @configname = 'clr enabled',
    @configvalue = 1;

RECONFIGURE;

Assembly and function

CREATE ASSEMBLY DBA
AUTHORIZATION dbo
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C010300B11134570000000000000000E00002210B010B00000C000000060000000000000E2A0000002000000040000000000010002000000002000004000000000000000400000000000000008000000002000000000000030040850000100000100000000010000010000000000000100000000000000000000000B42900005700000000400000A802000000000000000000000000000000000000006000000C0000007C2800001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000140A000000200000000C000000020000000000000000000000000000200000602E72737263000000A80200000040000000040000000E0000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000001200000000000000000000000000004000004200000000000000000000000000000000F0290000000000004800000002000500EC210000900600000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000133003008601000001000011020A0F01280600000A0F00280600000A590B160C160D072C5C0F00280700000A0F01280700000A30200F00280700000A0F01280700000A33140F00280800000A0F01280800000A31040717590B120007280900000A0A2B1D120017280A00000A03280B00000A2C5B0817580C120017280A00000A0A0603280C00000A2C451200280600000A7E0D00000A13051205280600000A33C31200280700000A7E0D00000A13061206280700000A33AC2B150917580D120023000000000000F03F280E00000A0A0603280C00000A2DE21F64730F00000A13040716313D1104076F1000000A26110407172E0772010000702B05720F0000706F1100000A2611040816300B091630077E1200000A2B05721B0000706F1100000A26081631391104086F1000000A26110408172E0772210000702B0572310000706F1100000A261104091630077E1200000A2B05721B0000706F1100000A2609163006072D24082D211104096F1000000A26110409172E07723F0000702B05724B0000706F1100000A2611046F1300000A2A1E02281400000A2A000042534A4201000100000000000C00000076322E302E35303732370000000005006C000000A8010000237E000014020000F001000023537472696E6773000000000404000058000000235553005C0400001000000023475549440000006C0400002402000023426C6F620000000000000002000001471502000900000000FA253300160000010000000A000000020000000200000003000000140000000500000001000000010000000200000000000A0001000000000006003D0036000600440036000A008E0073000600BB00A8001300CF0000000600FE00DE0006001E01DE000A00460173000600C501B9010600DA0136000000000001000000000001000100010010001800000005000100010050200000000096004D000A000100E22100000000861861001200040000000000000000000100A00000000200A500190061001200210061004800310061004E0039006100120041006100120011005B01B60111006401B60111006E01B60111007601BA0111007F01BA0111008901C00111009C01C0011100A801C8011100B101CC01490061004E004900D301D2014900D301D8015100E101DE010900E701E10109006100120020002B00530024000B0016002E001300F3012E001B00FC012E0023000502E5010480000000000000000000000000000000003C01000002000000000000000000000001002D000000000002000000000000000000000001006700000000000000003C4D6F64756C653E004461746162617365312E646C6C0055736572446566696E656446756E6374696F6E73006D73636F726C69620053797374656D004F626A656374004461746554696D6500496E74657276616C4465736372697074696F6E002E63746F720053797374656D2E44617461004D6963726F736F66742E53716C5365727665722E5365727665720053716C46616365744174747269627574650046726F6D00546F0053797374656D2E446961676E6F73746963730044656275676761626C6541747472696275746500446562756767696E674D6F6465730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C697479417474726962757465004461746162617365310053716C46756E6374696F6E417474726962757465006765745F59656172006765745F4D6F6E7468006765745F446179004164645965617273004164644D6F6E746873006F705F4C6573735468616E4F72457175616C006F705F4C6573735468616E004D617856616C756500416464446179730053797374656D2E5465787400537472696E674275696C64657200417070656E6400537472696E6700456D70747900546F537472696E6700000D200079006500610072007300000B2000790065006100720000052C002000000F20006D006F006E00740068007300000D20006D006F006E0074006800000B200064006100790073000009200064006100790000000000AFDAAB526E833740886DDFF9139712E60008B77A5C561934E0890700020E1109110903200001310100030054020D497346697865644C656E6774680054020A49734E756C6C61626C65005408074D617853697A656400000005200101111504200101088161010005005455794D6963726F736F66742E53716C5365727665722E5365727665722E446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038390A446174614163636573730000000054557F4D6963726F736F66742E53716C5365727665722E5365727665722E53797374656D446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038391053797374656D446174614163636573730000000054020F497344657465726D696E69737469630154020949735072656369736501540E044E616D6513496E74657276616C4465736372697074696F6E0320000805200111090807000202110911090306110905200111090D05200112250805200112250E02060E0320000E0D070711090808081225110911090801000200000000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F77730100000000B111345700000000020000001C01000098280000980A000052534453F841C8A989DDDC4098D9FD78225EB30502000000633A5C55736572735C7061756C775C4F6E6544726976655C446F63756D656E74735C56697375616C2053747564696F20323031355C50726F6A656374735C4461746162617365315C4461746162617365315C6F626A5C52656C656173655C4461746162617365312E706462000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000DC2900000000000000000000FE290000002000000000000000000000000000000000000000000000F02900000000000000000000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF2500200010000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001001000000018000080000000000000000000000000000001000100000030000080000000000000000000000000000001000000000048000000584000004C02000000000000000000004C0234000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000000000000000000000000000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B004AC010000010053007400720069006E006700460069006C00650049006E0066006F0000008801000001003000300030003000300034006200300000002C0002000100460069006C0065004400650073006300720069007000740069006F006E000000000020000000300008000100460069006C006500560065007200730069006F006E000000000030002E0030002E0030002E00300000003C000E00010049006E007400650072006E0061006C004E0061006D00650000004400610074006100620061007300650031002E0064006C006C0000002800020001004C006500670061006C0043006F00700079007200690067006800740000002000000044000E0001004F0072006900670069006E0061006C00460069006C0065006E0061006D00650000004400610074006100620061007300650031002E0064006C006C000000340008000100500072006F006400750063007400560065007200730069006F006E00000030002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000030002E0030002E0030002E0030000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000103A00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
WITH PERMISSION_SET = SAFE;
GO
CREATE FUNCTION dbo.IntervalDescription
(
    @From date, 
    @To date
)
RETURNS nvarchar(100)
AS EXTERNAL NAME 
    DBA.UserDefinedFunctions.IntervalDescription;

Usage

SELECT 
    TD.FromDate,
    TD.ToDate,
    TD.ExpectedResult, 
    IntervalDescription = dbo.IntervalDescription(TD.FromDate, TD.ToDate) 
FROM dbo.TestData AS TD;

Result

Plan

Output

Source

I am not a C# programmer!

using Microsoft.SqlServer.Server;
using System;
using System.Text;

public partial class UserDefinedFunctions
{
    [SqlFunction
        (
        DataAccess = DataAccessKind.None,
        SystemDataAccess = SystemDataAccessKind.None,
        IsDeterministic = true,
        IsPrecise = true,
        Name = "IntervalDescription"
        )
    ]
    [return: SqlFacet(IsFixedLength = false, IsNullable = false, MaxSize = 100)]
    public static string IntervalDescription(DateTime From, DateTime To)
    {
        var workDate = From;
        int years = To.Year - From.Year;
        int months = 0;
        int days = 0;

        if (years != 0)
        {
            if (From.Month > To.Month || (From.Month == To.Month && From.Day > To.Day))
            {
                years--;
            }
            workDate = workDate.AddYears(years);
        }

        while (workDate < To && (workDate.Year != DateTime.MaxValue.Year || workDate.Month != DateTime.MaxValue.Month))
        {
            if (workDate.AddMonths(1) <= To)
            {
                months++;
                workDate = workDate.AddMonths(1);
            }
            else
            {
                break;
            }
        }

        while (workDate < To)
        {
            days++;
            workDate = workDate.AddDays(1);
        }

        StringBuilder sb = new StringBuilder(100);

        if (years > 0)
        {
            sb.Append(years);
            sb.Append(years == 1 ? " year" : " years");
            sb.Append((months > 0 || days > 0) ? ", " : string.Empty);
        }

        if (months > 0)
        {
            sb.Append(months);
            sb.Append(months == 1 ? " month" : " months");
            sb.Append(days > 0 ? ", " : string.Empty);
        }

        if (days > 0 || (years == 0 && months == 0))
        {
            sb.Append(days);
            sb.Append(days == 1 ? " day" : " days");
        }

        return
            sb.ToString();

    }
}

The following solution is for SQL Server. The approach is similar to Serg's in that the query uses only the DATEADD and DATEDIFF functions. It does not, however, account for negative intervals (FromDate > ToDate), and it derives years and months from the total month difference:

WITH
  MonthDiff AS
  (
    SELECT
      t.FromDate,
      t.ToDate,
      t.ExpectedResult,
      Months = x.Months - CASE WHEN DAY(t.FromDate) > DAY(t.ToDate) THEN 1 ELSE 0 END
    FROM
      dbo.TestData AS t
      CROSS APPLY (SELECT DATEDIFF(MONTH, t.FromDate, t.ToDate)) AS x (Months)
  )
SELECT
  t.FromDate,
  t.ToDate,
  t.ExpectedResult,
  Result = ISNULL(NULLIF(ISNULL(x.Years  + CASE x.Years  WHEN '1' THEN ' year '  ELSE ' years '  END, '')
                       + ISNULL(x.Months + CASE x.Months WHEN '1' THEN ' month ' ELSE ' months ' END, '')
                       + ISNULL(x.Days   + CASE x.Days   WHEN '1' THEN ' day '   ELSE ' days '   END, ''), ''), '0 days')
FROM
  MonthDiff AS t
  CROSS APPLY
  (
    SELECT
      CAST(NULLIF(t.Months / 12, 0) AS varchar(10)),
      CAST(NULLIF(t.Months % 12, 0) AS varchar(10)),
      CAST(NULLIF(DATEDIFF(DAY, DATEADD(MONTH, t.Months, t.FromDate), t.ToDate), 0) AS varchar(10))
  ) AS x (Years, Months, Days)
;

Output:

FromDate    ToDate      ExpectedResult                 Result
----------  ----------  -----------------------------  -----------------------------
1999-12-31  1999-12-31  0 days                         0 days
1999-12-31  2000-01-01  1 day                          1 day 
2000-01-01  2000-02-01  1 month                        1 month 
2000-02-01  2000-03-01  1 month                        1 month 
2000-01-28  2000-02-29  1 month, 1 day                 1 month 1 day 
2000-01-01  2000-12-31  11 months, 30 days             11 months 30 days 
2000-02-28  2000-03-01  2 days                         2 days 
2001-02-28  2001-03-01  1 day                          1 day 
2000-01-01  2001-01-01  1 year                         1 year 
2000-01-01  2011-01-01  11 years                       11 years 
9999-12-30  9999-12-31  1 day                          1 day 
1900-01-01  9999-12-31  8099 years 11 months 30 days   8099 years 11 months 30 days 

My version, implemented in SQL Server 2008R2 SP2.

CREATE FUNCTION dbo.ReadableInterval(
    @FromDate AS date,
    @ToDate AS date
)
RETURNS TABLE AS RETURN 
(
with YearStep as
(
    select
        max(n1.Number) as YearNumber
    from dbo.Numbers as n1
    where n1.Number <= DATEDIFF(YEAR, @FromDate, @ToDate)  -- see comment (A)
    and DATEADD(YEAR, n1.Number, @FromDate) <= @ToDate     -- see comment (B)
)
, MonthStep as
(
    select
        max(n2.Number) as MonthNumber
    from dbo.Numbers as n2
    cross apply YearStep as y1
    where n2.Number <= DATEDIFF(MONTH, DATEADD(YEAR, y1.YearNumber, @FromDate), @ToDate)
    and DATEADD(MONTH, n2.Number, DATEADD(YEAR, y1.YearNumber, @FromDate)) <= @ToDate
)
, DayStep as
(
    select
        DATEDIFF(day, DATEADD(MONTH, m1.MonthNumber, DATEADD(YEAR, y2.YearNumber, @FromDate)), @ToDate) as DayNumber
    from MonthStep as m1
    cross apply YearStep as y2
)
select
    y.YearNumber,
    m.MonthNumber,
    d.DayNumber
from YearStep as y
cross apply MonthStep as m
cross apply DayStep as d
)

With the given test data the results are

select
    td.FromDate,
    td.ToDate,
    td.ExpectedResult,
    ri.YearNumber as Years,
    ri.MonthNumber as Months,
    ri.DayNumber as [Days]
from dbo.TestData as td
cross apply dbo.ReadableInterval(td.FromDate, td.ToDate) as ri;
FromDate   ToDate     ExpectedResult               Years Months Days
---------- ---------- ---------------------------- ----- ------ ----
1999-12-31 1999-12-31 0 days                           0      0    0
1999-12-31 2000-01-01 1 day                            0      0    1
2000-01-01 2000-02-01 1 month                          0      1    0
2000-02-01 2000-03-01 1 month                          0      1    0
2000-01-28 2000-02-29 1 month, 1 day                   0      1    1
2000-01-01 2000-12-31 11 months, 30 days               0     11   30
2000-02-28 2000-03-01 2 days                           0      0    2
2001-02-28 2001-03-01 1 day                            0      0    1
2000-01-01 2001-01-01 1 year                           1      0    0
2000-01-01 2011-01-01 11 years                        11      0    0
9999-12-30 9999-12-31 1 day                            0      0    1
1900-01-01 9999-12-31 8099 years 11 months 30 days  8099     11   30

Explanation

My general approach is to step forward from the earlier date, first in years, then months, then in days. At each level of granularity the objective is to get as close to the end date without going over it, then continue at the next lower level.

I use a numbers table to facilitate the close-to-but-not-over calculation. From this table and DATEADD I can find the largest number of years/ months/ days that precede ToDate - comment (B) in the code.

Since I was looking for the MAX number and my Numbers table is clustered on it, the optimizer was performing a descending scan, feeding values to DATEADD. This was causing date overflow errors as Numbers contains over 100,000 rows. DATEADD(YEAR, 100000, @FromDate) is greater than 9999-12-31 and an error is raised. Predicate (A) gives an upper limit on the Number value from which the backward scan starts, avoiding the date overflow. Consequently the query plan traverses very few rows for even very large date ranges.

This approach is used for finding years and months, except the starting point for months is brought forward by however many years I found in the first CTE. DAYS is my lowest level of granularity so a simple DATEDIFF is sufficient.

This could be extended to finer granularity, returning the interval in hours, minutes and seconds if required.