Bonjour
La syntaxe sera différente selon le “moteur” de base utilisé…
Avec Hsqldb (voir Base avec calcul de l’âge.odb):
SELECT "DateNaissance", "DateDécès",
CASE
WHEN "DateDécès" IS NULL THEN
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
ELSE
CASE
WHEN Month("DateDécès") < month("DateNaissance") or ( Month("DateDécès") = Month("DateNaissance") and day("DateDécès")<day("DateNaissance") ) THEN
CAST((year("DateDécès") - year("DateNaissance") -1) AS VARCHAR) || ' ans'
ELSE
CAST((year("DateDécès") - year("DateNaissance") ) AS VARCHAR) || ' ans'
END
END as "Age"
from "Table1"
Avec Firebird (voir CalculDateFB.odb):
SELECT "Nom", "DateNaissance", "DateDécès",
CASE
WHEN "DateDécès" IS NULL THEN
CASE
WHEN extract(Month from CURRENT_DATE) < "mois" or
( extract(Month from CURRENT_DATE) = "mois" and extract(day from CURRENT_DATE)<"jour" ) THEN
datediff(year, "DateNaissance", CURRENT_DATE) -1
ELSE
datediff(year, "DateNaissance", CURRENT_DATE)
END
ELSE
CASE
WHEN extract(Month from "DateDécès") < "mois" or
( extract(Month from "DateDécès") = "mois" and extract(day from "DateDécès")<"jour" ) THEN
datediff(year, "DateNaissance", "DateDécès") -1
ELSE
datediff(year, "DateNaissance", "DateDécès")
END
END as "Age"
from "VuePourAge"
Pour alléger la requête j’ai créé une vue “VuePourAge” dont le SQL est :
SELECT "ID_Table", "Nom", "DateNaissance", "DateDécès", EXTRACT( DAY FROM "DateNaissance" ) "jour", EXTRACT( MONTH FROM "DateNaissance" ) "mois", EXTRACT( YEAR FROM "DateNaissance" ) "an" FROM "Table1"
Cordialement