Mega Code Archive

 

Categories / MySQL / Date Time

 

Calculate an age as of the beginning 1975 for someone born on 1965-03-01

mysql> mysql> SET @birth = '1965-03-01'; Query OK, 0 rows affected (0.00 sec) mysql> SET @target = '1975-01-01'; Query OK, 0 rows affected (0.00 sec) mysql> SELECT @birth, @target,     -> YEAR(@target) - YEAR(@birth) AS 'difference',     -> IF(RIGHT(@target,5) < RIGHT(@birth,5),1,0) AS 'adjustment',     -> YEAR(@target) - YEAR(@birth)     -> - IF(RIGHT(@target,5) < RIGHT(@birth,5),1,0)     -> AS 'age'; +------------+------------+------------+------------+------+ | @birth     | @target    | difference | adjustment | age  | +------------+------------+------------+------------+------+ | 1965-03-01 | 1975-01-01 |         10 |          1 |    9 | +------------+------------+------------+------------+------+ 1 row in set (0.00 sec) mysql>