Get the Age of an Employee

To get the age of an employee you can use MONTHS_BETWEEN. However, since MONTHS_BETWEEN requires two dates and PeopleSoft auto-magically converts date fields to a character field (TO_CHAR) when a date is used as a field in your query and the table alias is used with the field, so you will need to convert the field(s) you are using back to a date type (TO_DATE).

This expression

MONTHS_BETWEEN(SYSDATE, A.BIRTHDATE)

Will look like this in the SQL tab

MONTHS_BETWEEN(SYSDATE,TO_CHAR(A.BIRTHDATE,'YYYY-MM-DD')

And of course when you run this, PeopleSoft Query will complain with the Oracle error message.

ORA-01861: literal does not match format string

The proper way is to do this.

MONTHS_BETWEEN(SYSDATE, TO_DATE(A.BIRTHDATE,'YYYY-MM-DD'))

Now the SQL will look like this and it will function

MONTHS_BETWEEN(SYSDATE, TO_DATE(TO_CHAR(A.BIRTHDATE,'YYYY-MM-DD'),'YYYY-MM-DD'))

Now that you have the "MONTHS_BETWEEN" your probably saying "I don't want to know the amount of months old the employee is. I want to know the amount of years old." So, now, we will want to divide the amount of months old the employee is by 12. There are 12 months in a year. Right?

MONTHS_BETWEEN(SYSDATE, TO_DATE(A.BIRTHDATE,'YYYY-MM-DD'))/12

And finally if you left the data type of the expressions as a character type you are probably saying "How do I get rid of these decimals?" While you can change the expression's data type to numeric and leave the decimal value blank or change it to a zero, the FLOOR function will do this for you.

FLOOR(MONTHS_BETWEEN(SYSDATE, TO_DATE(A.BIRTHDATE,'YYYY-MM-DD'))/12)

Some other useful age calculations

Age at Hire

FLOOR(MONTHS_BETWEEN(TO_DATE(B.HIRE_DT,'YYYY-MM-DD'), TO_DATE(A.BIRTHDATE,'YYYY-MM-DD'))/12)

Age at Termination

FLOOR(MONTHS_BETWEEN(TO_DATE(B.TERMINATION_DT,'YYYY-MM-DD'), TO_DATE(A.BIRTHDATE,'YYYY-MM-DD'))/12)

*NOTE: If you do not use the table (record) alias as a precursor to your field, the above is not necessary. However, this is not really a good idea as another table you are using may hold the same field value. You SQL will then not know which field you are referring too. Using table aliases, while taking longer to write the expression, is always better than not using table aliases. You could write the below statement and PeopleSoft Query will not auto-magically convert the date field to a character data type.

FLOOR(MONTHS_BETWEEN(SYSDATE, BIRTHDATE)/12)