Home > Database > mysql > Select the age from birthday in mysql ?

Select the age from birthday in mysql ?

Hi,

following  is user table:

CREATE TABLE IF NOT EXISTS `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`fname` varchar(255) NOT NULL,
`mname` varchar(255) NOT NULL,
`lname` varchar(255) NOT NULL,
`createdat` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`birthday` date NOT NULL,
PRIMARY KEY (`id`)
);

insert one row into table:

INSERT INTO `user` (`id`, `fname`, `mname`, `lname`, `createdat`, `birthday`) VALUES
(1, ‘Anil’, ‘Kumar’, ‘Panigrahi’, ‘2009-11-19 11:04:27’, ‘1983-02-14’),

To retrieve age from the table:

SELECT
 YEAR( NOW(  )  )  - YEAR( birthday )
 -
 IF( MONTH( NOW(  )  )  <
 MONTH( birthday ) , 1, 0  )  -
 IF( MONTH( NOW(  )  )  = MONTH( birthday )
AND
 DAYOFMONTH( NOW(  )  )  <
 DAYOFMONTH( birthday ) , 1, 0  )
AS
age,
birthday, now(  )
FROM user;

Hope that it will be useful.

3 Responses

  1. Jetz

    Gud day sir. .

    Can you please expound your code?? Im a newbie in php MySQL that’s why its very complicated for understand that code.

    Tnx. My Regards.

    -Jetz

  2. Hello Jetz,

    In the sql query,

    there three mysql functions used, that year(),month(), dayofmonth().

    If you run query like one by one means select year ,month and ….,

    In that case hope you will understand the code.

  3. Gregor

    Nice one, i do the same but with php, because i dont want to “olver load” or something like that in the sql code lol, too many querys that i have to manage u.u

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.