Mega Code Archive
Determining Ages in Months
<![CDATA[
mysql>
mysql> CREATE TABLE sibling
-> (
-> name CHAR(20),
-> birth DATE
-> );
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> INSERT INTO sibling (name,birth) VALUES('Gretchen','1942-04-14');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO sibling (name,birth) VALUES('Wilbur','1946-11-28');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO sibling (name,birth) VALUES('Franz','1953-03-05');
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> SELECT * FROM sibling;
+----------+------------+
| name | birth |
+----------+------------+
| Gretchen | 1942-04-14 |
| Wilbur | 1946-11-28 |
| Franz | 1953-03-05 |
+----------+------------+
3 rows in set (0.00 sec)
mysql>
mysql> SELECT name, birth, CURDATE( ) AS today,
-> (YEAR(CURDATE( )) - YEAR(birth)) * 12
-> + (MONTH(CURDATE( )) - MONTH(birth))
-> - IF(DAYOFMONTH(CURDATE( )) < DAYOFMONTH(birth),1,0)
-> AS 'age in months'
-> FROM sibling;
+----------+------------+------------+---------------+
| name | birth | today | age in months |
+----------+------------+------------+---------------+
| Gretchen | 1942-04-14 | 2011-10-03 | 833 |
| Wilbur | 1946-11-28 | 2011-10-03 | 778 |
| Franz | 1953-03-05 | 2011-10-03 | 702 |
+----------+------------+------------+---------------+
3 rows in set (0.00 sec)
mysql>
mysql> drop table sibling;
Query OK, 0 rows affected (0.00 sec)
]]>