To calculate age and put age category

1

I have year of birth of the Id in my datebase I want to calculate age and segregate as
age category as 1-10,11-20,21-30------61-70,70+
How can i do in toad

2

First reaction is that with only the Year of birth you can't determine the actual age with accuracy.
Second reaction is that if this is Oracle, any SQL Server code would need to be massaged to work in that environment.

select DateDiff(year, MyBirthYear, GetDate()) as ApproximateAge, -- This is inaccurate but close...'ish case when DateDiff(year, MyBirthYear, GetDate()) between 1 and 10 then '1-10' -- doesn't cover zero!!! when DateDiff(year, MyBirthYear, GetDate()) between 11 and 20 then '11-20' when DateDiff(year, MyBirthYear, GetDate()) between 21 and 30 then '21-30' when DateDiff(year, MyBirthYear, GetDate()) between 31 and 40 then '31-40' when DateDiff(year, MyBirthYear, GetDate()) between 41 and 50 then '41-50' when DateDiff(year, MyBirthYear, GetDate()) between 51 and 60 then '51-60' when DateDiff(year, MyBirthYear, GetDate()) between 61 and 70 then '61-70' else '70+' end AgeCategoryI hope this helps but I fear it won't.

1 Like
3

A better way to calculate age accurately (from a posting in SQL Team a while ago)

4 
CREATE TABLE #tmp(personId INT, DOB DATE);
INSERT INTO #tmp VALUES 
	(1,'20160229'), (2,'20030817'),(3,'19951005');
	
SELECT
	personId,
	Age,
	case 
		  when Age BETWEEN 0 AND 10 THEN '0-10' 
		  --.. etc
	END AS AgeCategory	
FROM
	#tmp
	CROSS APPLY
	( VALUES (
		(
		   CAST(CONVERT(CHAR(8), GETDATE(), 112) AS INT)
		   - 
		   CAST(CONVERT(CHAR(8), DOB, 112) AS INT)
	)/10000 )  )v(Age);

DROP TABLE #tmp;
1 Like
5

I have used case when then