How to use DATEDIFF to return year, month and day?

How can I use DATEDIFF to return the difference between two dates in years, months and days in SQL Server 2005

DATEDIFF (date , date)

How to result that: 2 year 3 month 10 day

Can anyone complete this t-sql?

ALTER FUNCTION [dbo].[gatYMD](@dstart VARCHAR(50), @dend VARCHAR(50))
RETURNS VARCHAR(50) AS
BEGIN
    DECLARE @yy INT
    DECLARE @mm INT
    DECLARE @getmm INT
    DECLARE @dd INT

    SET @yy = DATEDIFF(yy, @dstart, @dend)
    SET @mm = DATEDIFF(mm, @dstart, @dend)
    SET @dd = DATEDIFF(dd, @dstart, @dend)
    SET @getmm = ABS(DATEDIFF(mm, DATEADD(yy, @yy, @dstart), @dend))

    RETURN (
        Convert(varchar(10),@yy) + 'year' + Convert(varchar(10),@mm) + 'month'  + Convert(varchar(10),@dd) + 'day'
        )
END

Answer

Here’s my solution to Eric’s function:

DECLARE @getmm INT
DECLARE @getdd INT

SET @yy = DATEDIFF(yy, @dstart, @dend)
SET @mm = DATEDIFF(mm, @dstart, @dend)
SET @dd = DATEDIFF(dd, @dstart, @dend)
SET @getmm = ABS(DATEDIFF(mm, DATEADD(yy, @yy, @dstart), @dend))
SET @getdd = ABS(DATEDIFF(dd, DATEADD(mm, DATEDIFF(mm, DATEADD(yy, @yy, @dstart), @dend), DATEADD(yy, @yy, @dstart)), @dend))

RETURN (
  Convert(varchar(10),@yy) + 'year' + Convert(varchar(10),@getmm) + 'month'  + Convert(varchar(10),@getdd) + 'day'
)

Good call on the use of ABS to handle if the start date is after the end date.

---

This:

WITH ex_table AS (
  SELECT '2007-01-01' 'birthdatetime',
         '2009-03-29' 'visitdatetime')
SELECT CAST(DATEDIFF(yy, t.birthdatetime, t.visitdatetime) AS varchar(4)) +' year '+
       CAST(DATEDIFF(mm, DATEADD(yy, DATEDIFF(yy, t.birthdatetime, t.visitdatetime), t.birthdatetime), t.visitdatetime) AS varchar(2)) +' month '+
       CAST(DATEDIFF(dd, DATEADD(mm, DATEDIFF(mm, DATEADD(yy, DATEDIFF(yy, t.birthdatetime, t.visitdatetime), t.birthdatetime), t.visitdatetime), DATEADD(yy, DATEDIFF(yy, t.birthdatetime, t.visitdatetime), t.birthdatetime)), t.visitdatetime) AS varchar(2)) +' day' AS result
  FROM ex_table t

..or non-CTE using for SQL Server 2000 and prior:

SELECT CAST(DATEDIFF(yy, t.birthdatetime, t.visitdatetime) AS varchar(4)) +' year '+
       CAST(DATEDIFF(mm, DATEADD(yy, DATEDIFF(yy, t.birthdatetime, t.visitdatetime), t.birthdatetime), t.visitdatetime) AS varchar(2)) +' month '+
       CAST(DATEDIFF(dd, DATEADD(mm, DATEDIFF(mm, DATEADD(yy, DATEDIFF(yy, t.birthdatetime, t.visitdatetime), t.birthdatetime), t.visitdatetime), DATEADD(yy, DATEDIFF(yy, t.birthdatetime, t.visitdatetime), t.birthdatetime)), t.visitdatetime) AS varchar(2)) +' day' AS result
  FROM (SELECT '2007-01-01' 'birthdatetime',
         '2009-03-29' 'visitdatetime') t

…will return:

result
----------------------
2 year 2 month 28 day

Reference: DATEDIFF