PhuocLe - Microsoft Dynamics CRM

Get years, months, days between 2 date Nov/08/2013


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


Category: SQL
Tags:

Enter your comment

Your Name (*)
Your Email (*)
Website (*)