Clever Geek Handbook

How to show age based on specific years?

I am trying to show ages according to a certain age rank.

Here's a 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 a query, it displays all age conversions.

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

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

I am trying to show ages from 0 to 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, can anyone help or advise me?

Thanks in advance.

+3


source to share


3 answers


Your request 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 also use your index in date_birth (if any).

+3


source


Modify your query like 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
+2


source


I prefer to use variables in the request in such a way that you don't have to reset them

SELECT YEAR(CURDATE())- year(date_birth) AS ages
FROM clients
CROSS JOIN(SELECT @start := 0, @end := 22)t
HAVING ages BETWEEN @start AND @end

DEMO

+1


source






More articles:


All Articles