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:

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

Hope that it will be useful.

Categories: mysql

3 Comments

Jetz · January 17, 2010 at 4:53 am

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

Anil Kumar Panigrahi · January 25, 2010 at 6:15 am

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.

Gregor · October 6, 2013 at 9:13 pm

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

Avatar placeholder

Your email address will not be published. Required fields are marked *