Convert INT INTO DATE to calculate AGE

Question

I have a problem with a query on PostgreSQL. I have a case for school where I need calculate the age of a person when he register for school on 1st Septembre of 2005. In a file I have their date of birth and only the YEAR of school registration.

The year registration is a int type ('2005') but I need to convert it to 01-09-2005 (DD-MM-YYYY) which is the start of the school year. How can I do this ?

• Should I convert it necessary into a date type if I want to calculate the difference between the date of registration and the date of birth ?

• If yes, how can I convert it in order for the date of birth to be on the date type and have a format '01-09-2005'.

I have an answer but that's not the way I should do it :

SELECT AGE('01/09/2005', auditeur.date_nais)
FROM auditeur
JOIN inscription ON auditeur.id_auditeur = inscription.id_auditeur

What I want is to replace the '01/09/2005' with annee ('2005') which is an int type.

Answer 1

That's how you get the desired date from an integer:

(2005 || '-09-01')::date

The concatenation force-converts 2005 to text. Use ISO 8601 format YYYY-MM-DD. Then the cast to date does not depend on the current datestyle setting.

SELECT age((i.annee || '-09-01')::date, a.date_nais) AS age_at_registration
FROM   auditeur a
JOIN   inscription i USING (id_auditeur)
WHERE  a.id = 123; -- select person somehow

Related:

• How to concatenate columns in a Postgres SELECT?
• Selecting records between two timestamps

Answer 2

There is no need to generate a string to then convert to a date. You have 3 integers: annee from a table and constant values 09 for month and 01 for day. With these just use the make_date function which takes 3 integers and returns a date.

select  age( make_date (annee,09,01), auditeur.date_nais)
from auditeur 
join inscription 
  on auditeur.id_auditeur = inscription.id_auditeur