Faq/Base/121

How can I calculate an age from a date of birth?

 * Given a field containing dates of birth, calculate current age.



The query will be

SELECT "DateNaissance", CASE WHEN MONTH(CURRENT_DATE) < MONTH("DateNaissance") OR ( MONTH(CURRENT_DATE) = MONTH("DateNaissance") AND DAY(CURRENT_DATE) < DAY("DateNaissance") ) THEN CAST((YEAR(CURRENT_DATE) - YEAR("DateNaissance") -1) AS VARCHAR) || ' ans' ELSE CAST((YEAR(CURRENT_DATE) - YEAR("DateNaissance") ) AS VARCHAR) || ' ans'END AS "Age"FROM "Table1"

To calculate age to the nearest day:



The query looks like this

SELECT "DateNaissance", "Nom", YEAR(NOW)-YEAR("DateNaissance")- CASE WHEN (MONTH( NOW ) < MONTH("DateNaissance") OR ( MONTH( NOW ) = MONTH("DateNaissance") AND DAY( NOW ) < DAY ("DateNaissance"))) THEN 1 ELSE 0 END AS "ans", MONTH(NOW)-MONTH("DateNaissance") + CASE WHEN MONTH(now) <= MONTH("DateNaissance") AND DAY(NOW) < DAY("DateNaissance") THEN 11 ELSE CASE WHEN MONTH(NOW) < MONTH("DateNaissance") AND DAY(NOW) >= DAY("DateNaissance") THEN 12 ELSE CASE WHEN MONTH(NOW) > MONTH("DateNaissance") AND DAY(NOW) < DAY("DateNaissance") THEN -1 ELSE 0 END END END AS "mois", DATEDIFF('DAY', CAST(YEAR(NOW) || '-' || RIGHT( 0 || MONTH(NOW) - casewhen DAY(NOW) < DAY("DateNaissance") THEN 1 ELSE 0 END, 2) || '-' || RIGHT( 0 || DAY("DateNaissance"), 2) AS DATE), NOW ) AS "jours" FROM "Table1"


 * Download an example database