> In mySQL, I store dates as YYYY-MM-DD, a standard DATE type. It stores
users
> date of births. I need to calculate in a PHP script, the users age from
this
> DOB. I get a PHP date in the same format as the mySQL and subtract, which
> returns the year rounded off. ie, it doesnt matter if your birthdays in
june
> of 1983 and the date is januray 2003, your age is still returned as 20,
when
> it should be 19.
>
> Does anyone know how can i get the right age?
Okay, so you just want to know the age in years. Must you use php? Here is a
solution using mysql :
SELECT name, dob, CURDATE() as today,
( YEAR(CURDATE()) - YEAR(dob) ) +
LEAST( SIGN( DAYOFYEAR(CURDATE()) - DAYOFYEAR(dob) ), 0) as age_in_years
FROM test_age;
Here is the output :
+------+------------+------------+--------------+
| name | dob | today | age_in_years |
+------+------------+------------+--------------+
| jim | 1990-08-02 | 2003-08-03 | 13 |
| paul | 1990-08-03 | 2003-08-03 | 13 |
| tom | 1990-08-04 | 2003-08-03 | 12 |
| matt | 1990-09-01 | 2003-08-03 | 12 |
| sam | 1990-12-31 | 2003-08-03 | 12 |
| sam | 1991-01-01 | 2003-08-03 | 12 |
| sam | 1991-07-02 | 2003-08-03 | 12 |
| sam | 1991-08-02 | 2003-08-03 | 12 |
| sam | 1991-09-02 | 2003-08-03 | 11 |
+------+------------+------------+--------------+
9 rows in set (0.01 sec)
Here is how I generated my test data :
DROP TABLE IF EXISTS test_age;
CREATE TABLE test_age (
id int(6) NOT NULL auto_increment,
name varchar(20) default NULL,
dob date default NULL,
PRIMARY KEY (id)
) TYPE=MyISAM;
INSERT INTO test_age (id, name, dob) VALUES ('', 'jim', '1990-08-02');
INSERT INTO test_age (id, name, dob) VALUES ('', 'paul', '1990-08-03');
INSERT INTO test_age (id, name, dob) VALUES ('', 'tom', '1990-08-04');
INSERT INTO test_age (id, name, dob) VALUES ('', 'matt', '1990-09-01');
INSERT INTO test_age (id, name, dob) VALUES ('', 'sam', '1990-12-31');
INSERT INTO test_age (id, name, dob) VALUES ('', 'sam', '1991-01-01');
INSERT INTO test_age (id, name, dob) VALUES ('', 'sam', '1991-07-02');
INSERT INTO test_age (id, name, dob) VALUES ('', 'sam', '1991-08-02');
INSERT INTO test_age (id, name, dob) VALUES ('', 'sam', '1991-09-02');
> -----Original Message-----
> From: John Ryan [mailto:celticfc@iol.ie]
> Sent: Saturday, August 02, 2003 2:31 PM
> To: php-general@lists.php.net; php-db@lists.php.net
> Subject: subtracting dates...
>
>
> Hi,
>
> In mySQL, I store dates as YYYY-MM-DD, a standard DATE type. It stores
> users date of births. I need to calculate in a PHP script, the users age
> from this DOB. I get a PHP date in the same format as the mySQL and
> subtract, which returns the year rounded off. ie, it doesnt matter if
> your birthdays in june of 1983 and the date is januray 2003, your age is
> still returned as 20, when it should be 19.
>
> Does anyone know how can i get the right age?
>
>
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.506 / Virus Database: 303 - Release Date: 01/08/2003
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
