List of distinct years between two dates

Question

I'd like to retrieve the list of distinct years between two dates.

For example, from 25/12/2006 to 14/11/2013, the result should look like:

2006
2007
2008
2009
2010
2011
2012
2013

Is this possible in SQL Server ?

Answer 1

A date like 1/2/2013 is ambiguous: depending on the regional setting, it can be either Feb 1st, or Jan 2nd. So it's a good idea to use the YYYY-MM-DD date format when talking to the datebase.

You can generate a list of numbers using a recursive CTE:

; with  CTE as
        (
        select  datepart(year, '2006-12-25') as yr
        union all
        select  yr + 1
        from    CTE
        where   yr < datepart(year, '2013-11-14')
        )
select  yr
from    CTE

Example at SQL Fiddle.

Answer 2

CREATE FUNCTION [dbo].Fn_Interval 
(   
    @p1 datetime2, 
    @p2 datetime2
)
RETURNS @dt TABLE 
(
    YearValue int not null
)
AS
BEGIN

    declare @start as int
    declare @end as int

    set @start = DATEPART(year, @p1);
    set @end = DATEPART(year, @p2);

    while (@start <= @end)
    begin
        INSERT @dt
        SELECT @start;
        set @start = @start + 1;
    end

    RETURN;
END

Open a Query and try:

declare @p1 as datetime2;
declare @p2 as datetime2;

set @p1 = '2013-12-31';
set @p2 = '2020-05-05';

select *
from dbo.Fn_Interval(@p1, @p2)

Answer 3

Tested on SQL Server 2008

declare @smaller_date date = convert(date, '25/12/2006', 103)
declare @larger_date date = convert(date, '14/11/2013', 103)
declare @diff int
select @diff = DATEDIFF(YY, @smaller_date,  @larger_date)

;with sequencer(runner) as(
select 0 
union all
select sequencer.runner + 1 from sequencer
where runner < @diff
)
select YEAR(@smaller_date) + runner from sequencer