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
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
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?
It is date of birth
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]
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
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