scott.pletcher

CREATE TABLE #T(Id INT,Date1 DATETIME,Date2 DATETIME)

INSERT INTO #T

SELECT 1,'2/1/2007','1/31/2010' UNION ALL

SELECT 2,'2/1/2007','2/1/2010' UNION ALL

SELECT 3, '2/1/2007','1/15/2010'

SELECT Id, Date1 AS 'Start', Date2 AS 'End', DATEDIFF(YEAR, date1, date2)

+ CASE WHEN CONVERT(CHAR(5), date1, 1) = CONVERT(CHAR(5), date2, 1)

THEN 0 ELSE 1 END Years

FROM #T

Yields:

IdStart End Years

12007-02-01 00:00:00.0002010-01-31 00:00:00.0004

22007-02-01 00:00:00.0002010-02-01 00:00:00.0003

32007-02-01 00:00:00.0002010-01-15 00:00:00.0004

I think what you meant to write:

SELECT Id, Date1 AS 'Start', Date2 AS 'End', DATEDIFF(YEAR, date1, date2)

+ CASE WHEN CONVERT(CHAR(5), date1, 1) = CONVERT(CHAR(5), date2, 1)

THEN 1 ELSE 0 END Years

FROM #T

IdStart End Years

12007-02-01 00:00:00.0002010-01-31 00:00:00.0003

22007-02-01 00:00:00.0002010-02-01 00:00:00.0004

32007-02-01 00:00:00.0002010-01-15 00:00:00.0003