Case expressions are short-circuit evaluated. The first expression that meets the criteria will be used.

This one assumes that birth dates in the future and NULL birth dates should return a NULL age. Your business requirements may vary in regard to missing or invalid data.

CREATE TABLE test (dob DATE);

-- add more values here for a complete test

INSERT TEST VALUES

('2002-09-28'),

('2002-09-30'),

('2002-09-29'),

('2000-01-01'),

('1995-01-01'),

('1990-01-01'),

(NULL),

-- date in the future

('2020-01-01');;

SELECT dob,

CASE

WHEN dob <= DATEADD(YEAR, -66, GETDATE()) THEN 'Age 66+'

WHEN dob <= DATEADD(YEAR, -56, GETDATE()) THEN 'Age 56 to 65'

WHEN dob <= DATEADD(YEAR, -46, GETDATE()) THEN 'Age 46 to 55'

WHEN dob <= DATEADD(YEAR, -36, GETDATE()) THEN 'Age 36 to 45'

WHEN dob <= DATEADD(YEAR, -26, GETDATE()) THEN 'Age 26 to 35'

WHEN dob <= DATEADD(YEAR, -21, GETDATE()) THEN 'Age 21 to 25'

WHEN dob <= DATEADD(YEAR, -19, GETDATE()) THEN 'Age 19 to 20'

WHEN dob <= DATEADD(YEAR, -18, GETDATE()) THEN 'Age 18'

WHEN dob <= DATEADD(YEAR, -16, GETDATE()) THEN 'Age 16 to 17'

WHEN dob <= DATEADD(YEAR, -14, GETDATE()) THEN 'Age 14 to 15'

WHEN dob <= GETDATE() THEN 'Age less than 14'

END AS Age

from test

order by dob;