Fantastically simple!

As for how to get the duration in YMD, here's what I came up with. I tried to code it the way I would process it in my head (scary, I know!). My approach is that we humans would intuitively account for the differences in the days of the month and leap years by subtracting the dates in part.

1) first the whole years difference

2) then the number of whole months left over from the whole years difference

3) then the number of days left over from the whole months difference. Thus, the difference in days is dependent on the number of days in the month from the start date.

Anyway, for what it's worth here's the SQL I came up with.

DECLARE @StartTime datetime, @EndTime datetime;

SET @StartTime = '1964-07-24';

SET @EndTime = '2014-01-17';

SELECT

CONVERT(varchar(4),

CASE WHEN DATEPART(MONTH,@EndTime) < DATEPART(MONTH,@StartTime)

-- If month of @EndTime is < the month of @StartTime, then there is a fraction of a year between the two dates, so subtract 1 from the year difference

THEN DATEPART(YEAR,@EndTime) - DATEPART(YEAR,@StartTime) - 1

-- If the month of @EndTime is >= the month of @StartTime, then difference the years and take one away if there is only a fraction of a month between the dates (i.e. the day of @EndTime < the day of @StartTime)

ELSE DATEPART(YEAR,@EndTime) - DATEPART(YEAR,@StartTime) - CASE WHEN DATEPART(DAY,@EndTime) < DATEPART(DAY,@StartTime) THEN 1 ELSE 0 END

END

) + 'Y ' +

CONVERT(varchar(4),

CASE WHEN DATEPART(MONTH,@EndTime) <= DATEPART(MONTH,@StartTime)

-- If the month of @EndTime <= the month of @StartTime, then get the difference in months by adding 12 (i.e. one year) to @EndTime

THEN DATEPART(MONTH,@EndTime) + 12 - DATEPART(MONTH,@StartTime)

-- Otherwise, simply subtract the month of @StartTime from the month of @EndTime

ELSE DATEPART(MONTH,@EndTime) - DATEPART(MONTH,@StartTime)

END

-- If month of @EndTime is < month of @StartTime, then there is a fraction of a year between the two dates, so subtract 1 from the month difference

- CASE WHEN DATEPART(DAY,@EndTime) < DATEPART(DAY,@StartTime) THEN 1 ELSE 0 END

-- If the months are the same and the day of @EndTime is >= the day of @StartTime, then subtract 12 to prevent 12M from occurring in the output

-- The logic above for the year difference accounts for the full year

- CASE WHEN DATEPART(MONTH,@EndTime) = DATEPART(MONTH,@StartTime) AND DATEPART(DAY,@EndTime) >= DATEPART(DAY,@StartTime) THEN 12 ELSE 0 END

) + 'M ' +

CONVERT(varchar(4),

CASE WHEN DATEPART(DAY,@EndTime) < DATEPART(DAY,@StartTime)

-- If the day of @EndTime is < the day of @StartTime, then subtract the difference between @StartTime - @EndTime from the number of days in the month of @StartTime

-- This give the intuitive difference in days based on the @StartTime after getting the difference in years and months above.

THEN DATEDIFF(DAY, DATEADD(DAY, 1 - DAY(@StartTime), @StartTime), DATEADD(MONTH, 1, DATEADD(DAY, 1 - DAY(@StartTime), @StartTime))) -- Number of days in the month of @StartTime

- (DATEPART(DAY,@StartTime) - DATEPART(DAY,@EndTime))

-- Otherwise, simply subtract the day of @StartTime from the day of @EndTime

ELSE DATEPART(DAY,@EndTime) - DATEPART(DAY,@StartTime)

END

) + 'D '

AS 'Time Period'