Friday, 18 June 2010

calculating age in sql database with date of birth

dob
-------------
1973-12-01
1974-12-01
1975-12-01
1976-12-01
1977-12-01
1978-12-01
1979-12-01
1986-01-01



select dob, CURDATE(), (YEAR(CURDATE())-YEAR(dob)) - (RIGHT(CURDATE(),5) As age from `main_tb`;

dob|CURDATE()|age
---------------------------------
1973-12-01 | 2010-06-18 | 36
1974-12-01 | 2010-06-18 | 35
1975-12-01 | 2010-06-18 | 34
1976-12-01 | 2010-06-18 | 33
1977-12-01 |2010-06-18 | 32
1978-12-01 | 2010-06-18 | 31
1979-12-01 |2010-06-18 | 30
1986-01-01 | 2010-06-18 | 24



SELECT dob, CURDATE( ) , (
YEAR( CURDATE( ) ) - YEAR( dob )
) - ( RIGHT( CURDATE( ) , 5 ) < RIGHT( dob, 5 ) ) AS age, (
RIGHT( CURDATE( ) , 5 )
) AS var1, (
RIGHT( dob, 5 )
) AS var2, (
RIGHT( CURDATE( ) , 5 ) < RIGHT( dob, 5 )
) AS var3
FROM `main_tb`
LIMIT 0 , 30;

dob||CURDATE()||age||var1||var2||var3|
----------------------------------------------
1973-12-01|2010-06-18|36|06-18|12-01|1
1974-12-01|2010-06-18|35|06-18|12-01|1
1975-12-01|2010-06-18|34|06-18|12-01|1
1976-12-01|2010-06-18|33|06-18|12-01|1
1977-12-01|2010-06-18|32|06-18|12-01|1
1978-12-01|2010-06-18|31|06-18|12-01|1
1979-12-01|2010-06-18|30|06-18|12-01|1
1986-01-01|2010-06-18|24|06-18|01-01|0|

No comments:

Labels

Search This Blog