Hi all, I would like to know the f

[Gius]

Hi all,
I would like to know the fastest (months_between, to_number, etc) way to compute the difference in years between two date fields.
Any help will be appreciated.

Thanks


Giuseppe

 

[THoey]

Why use a function, when Oracle will let you subtract dates?

Table Test
StartDate
StopDate

One record created:

StartDate = "01-DEC-01"
StopDate = "07-DEC-01"

select enddate - startdate from test;

ENDDATE-STARTDATE
-----------------
6
Terry M. Hoey

 

[Gius]

Thank you,
But I need the difference in years (actually the age of a list of customers).

Table Test
Customer
BirthDate

BirthDate = "03-MAR-1950"

select sysdate- birthdate from test;

ENDDATE-STARTDATE
-----------------
18894.94

It is the difference in days,I know I can divide this result by 365 to receive the age, but I was wondering if there is a faster way.

Giuseppe

 

[karluk]

You definitely shouldn't calculate days and then divide by 365, or even 365.25. That calculation will sometimes be wrong because of leap years.

I think you answered your own question. I don't see any reason not to use the months_between function and then divide by 12. You can also truncate the result if you want to express age in the usual way - an exact integer that doesn't change until your next birthday.

select select trunc(months_between(sysdate,birthdate)/12) from test;