----Difference between two dates in years, months and days
--USE AdventureWorks2014
--GO

SELECT BirthDate, GETDATE() As Today, DATEDIFF(DAY, BirthDate,GETDATE()),
	CASE 
		WHEN MONTH(BirthDate) > MONTH(GETDATE()) OR 
				(MONTH(BirthDate) = MONTH(GETDATE()) AND DAY(BirthDate) > DAY(GETDATE()))
			THEN DATEDIFF(YEAR, BirthDate,GETDATE()) - 1
		ELSE
			DATEDIFF(YEAR, BirthDate,GETDATE()) END AS 'Years',
	CASE
		WHEN DATEDIFF(MONTH,BirthDate, GETDATE()) % 12 = 1 
			THEN 1
		WHEN MONTH(BirthDate) = MONTH(GETDATE()) AND DAY(BirthDate) > DAY(GETDATE())
			THEN 12 + DATEDIFF(MONTH,BirthDate, GETDATE()) % 12 - 1
		WHEN MONTH(BirthDate) < MONTH(GETDATE()) AND DAY(BirthDate) < DAY(GETDATE())
			THEN DATEDIFF(MONTH,BirthDate, GETDATE()) % 12
		WHEN MONTH(BirthDate) < MONTH(GETDATE()) AND DAY(BirthDate) > DAY(GETDATE())
			THEN DATEDIFF(MONTH,BirthDate, GETDATE()) % 12 - 1
		WHEN MONTH(BirthDate) = MONTH(GETDATE()) AND DAY(BirthDate) < DAY(GETDATE())
			THEN 0
		WHEN MONTH(BirthDate) > MONTH(GETDATE()) AND DAY(BirthDate) > DAY(GETDATE())
			THEN DATEDIFF(MONTH,BirthDate, GETDATE()) % 12 - 1
		ELSE 
			DATEDIFF(MONTH,BirthDate, GETDATE()) % 12 END AS 'Months',
	CASE
		WHEN (MONTH(Birthdate) < MONTH(GETDATE()) AND DAY(BirthDate) > DAY(GETDATE()))
				OR (MONTH(BirthDate) > MONTH(GETDATE()) AND DAY(BirthDate) > DAY(GETDATE()))
				OR (MONTH(BirthDate) = MONTH(GETDATE()) AND DAY(BirthDate) > DAY(GETDATE()))
			THEN day(DATEADD(DAY, -DAY(GETDATE()) ,DATEADD(MONTH, -1, GETDATE()))) - DAY(BirthdATE) + day(getdate())
		WHEN MONTH(Birthdate) < MONTH(GETDATE()) AND DAY(BirthDate) < DAY(GETDATE())
			THEN DAY(GETDATE()) - DAY(BirthDate)
		WHEN MONTH(BirthDate) > MONTH(GETDATE()) AND DAY(BirthDate) < DAY(GETDATE())
			THEN DAY(GETDATE()) - DAY(BirthDate)
		WHEN MONTH(BirthDate) = MONTH(GETDATE()) AND DAY(BirthDate) < DAY(GETDATE())
			THEN DAY(GETDATE()) - DAY(BirthDate)
		WHEN  DAY(BirthDate) = DAY(GETDATE()) 
			THEN 0 END AS 'Days'
FROM HumanResources.Employee

/* Test Results: 
	29/01/69 - 46 years 7 months 16 days
	1/08/71 - 44 years 1 month 13 days
	12/11/74 - 40 years 10 months 2 days
	23/12/74 - 40 years 8 months 22 days
	27/09/59 - 62 years 11 months 18 days
	11/03/59 - 56 years 6 months 3 days
	24/02/87 - 28 Years, 6 months, 21 days
	13/09/63 - 53 years, 0 months, 1 day
*/