Sometime you want get how many years, months, days from 2 date.
The UDF here help you
CREATE FUNCTION [dbo].[fnGetYMD]
(
@d1 DATETIME,
@d2 DATETIME
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
IF (@d1 >= @d2) RETURN '0 day'
DECLARE @y INT
DECLARE @m INT
DECLARE @d INT
SET @y = DATEDIFF(yy, @d1, @d2) - CASE WHEN (MONTH(@d1) > MONTH(@d2)) OR (MONTH(@d1) = MONTH(@d2) AND DAY(@d1) > DAY(@d2)) THEN 1 ELSE 0 END
SET @d1 = DATEADD(yy, @y, @d1)
SET @m = DATEDIFF(m, @d1, @d2) - CASE WHEN DAY(@d1) > DAY(@d2) THEN 1 ELSE 0 END
SET @d1 = DATEADD(m, @m, @d1)
SET @d = DATEDIFF(d, @d1, @d2)
DECLARE @ret NVARCHAR(MAX) = ''
IF @y <> 0
BEGIN
IF @y = 1
SET @ret = '1 year '
ELSE
SET @ret = CONVERT(NVARCHAR(MAX), @y) + ' years '
END
IF @m <> 0
BEGIN
IF @m = 1
SET @ret = @ret + '1 month '
ELSE
SET @ret = @ret + CONVERT(NVARCHAR(MAX), @m) + ' months '
END
IF @d <> 0
BEGIN
IF @d = 1
SET @ret = @ret + '1 day'
ELSE
SET @ret = @ret + CONVERT(NVARCHAR(MAX), @d) + ' days'
END
RETURN @ret
END
And the result from my birthday until to 11-08-2013