trunc in Oracle SQL-CodePudding

I try to used TRUNC in SQL Oracle but doens't work . I make a mistake ?

select TRUNC(cal.DAY_DATE - BIRTHDATE)/ 365 AS EMP_AGE,

from dual

CodePudding user response：

See the example below to accomplish what you are wanting to do. Just replace YOUR_TABLE with the table you want, and replace YOUR_BIRTHDAY_COLUMN with the name of the column that contains the birth date in the table you are querying from.

select FLOOR((trunc(sysdate) - (trunc(YOUR_BIRTHDAY_COLUMN)))/365) as AGE from YOUR_TABLE;

CodePudding user response：

If you want to calculate the number of days between two dates and then divide by 365 and then round down, you can use:

SELECT TRUNC(
         (SYSDATE - BIRTHDATE)
         / 365
       ) AS EMP_AGE
FROM   employees;

There are not always 365 days in a year so your calculation is going to be slightly wrong.

It is going to be more accurate to count the number of months using:

SELECT TRUNC(
         MONTHS_BETWEEN(SYSDATE, birthdate)
         / 12
       ) AS emp_age
FROM   employees;