SQLTeam.com | Weblogs | Forums

Get age in years and months


#1

Hi,

How do I calculate the age in years and months?
Lets say if I am 30yrs old and my birthday is in October I want to show that the user is 30.9
If my birthday was in June then I am 30.1
How do I do that?

Thanks


#2

What is the input you have? Is it date of birth, or is it current age and month of birth, or is it something else?


#3

It is date of birth


#4

See this example:

DECLARE @DateOfBirth DATE = '20131022';
SELECT
	( CAST(CONVERT(CHAR(8), GETDATE(), 112) AS INT) -  CAST(CONVERT(CHAR(8), @DateOfBirth, 112) AS INT) )/10000 [Years],
	(12+MONTH(GETDATE()) - MONTH(@DateOfBirth))%12  + 
	CASE WHEN MONTH(GETDATE()) = MONTH(@DateOfBirth) AND DAY(@DateOfBirth) > DAY(GETDATE()) THEN 12 ELSE 0 END [Months]

#5

You could also try:

select BIRTHDATE,
CONVERT(decimal(10,1), datediff(day,birthdate,'2015-07-22')/365.0,1) AS 'ELAPSED'
from SQLTest

It will give you this:

BIRTHDATE	ELAPSED
1991-03-19	24.4
1962-05-28	53.2
1964-05-18	51.2
1987-05-23	28.2

#6

CAST(DATEDIFF(MONTH, birth_date, GETDATE()) / 12 AS varchar(3)) + '.' +
CAST(DATEDIFF(MONTH, birth_date, GETDATE()) % 12 AS varchar(2))

For example:

SELECT 
    CAST(DATEDIFF(MONTH, birth_date, GETDATE()) / 12 AS varchar(3)) + '.' +
    CAST(DATEDIFF(MONTH, birth_date, GETDATE()) % 12 AS varchar(2))
FROM (
    SELECT CAST('20150101' AS date) AS birth_date UNION ALL
    SELECT '20140204'
) AS test_data