How can show ages according specific years?

Question

I'm trying to show ages according a specific rank of ages.

Here is the demo:

CREATE TABLE clients
(date_birth date, date_anniversary date);

INSERT INTO clients
(date_birth, date_anniversary)
VALUES
('1991-01-04',NULL ),
('1992-01-05',NULL ),
('1993-01-06',NULL ),
('1994-01-07',NULL ),
('1995-01-08',NULL ),
('1996-01-09',NULL ),
('1997-01-10',NULL ),
('1998-01-11',NULL ),
('1999-08-12',NULL ) ;

Here is the query,it shows all ages converted.

SET @start:='0'; 
SET @end:='22';

SELECT YEAR(CURDATE())- year(date_birth) AS ages
FROM clients

I'm trying to show ages between 0 AND 22, I tried this demo :

SET @start:='0'; 
SET @end:='22';

SELECT YEAR(CURDATE())- year(date_birth) AS ages
FROM clients
WHERE year(date_birth) >= @start AND year(date_birth) <= @end

Please somebody can help me or advice me?

Thanks in advance.

Answer 1

Your query should be

SELECT YEAR(CURDATE())- year(date_birth) AS ages
FROM clients
WHERE date_birth <= (curdate() - interval @start year) 
and date_birth >= (curdate() - interval @end year)

This will make use of your index on date_birth as well (if any).

Answer 2

Change your query to be this:

SET @start:='0'; 
SET @end:='22';

SELECT YEAR(CURDATE())- year(date_birth) AS ages
FROM clients
WHERE YEAR(CURDATE())- year(date_birth) >= @start 
  AND YEAR(CURDATE())- year(date_birth) <= @end