Playing with Dates 1

[LaurenM]

Hey All, I work for a pension plan, and we need to know when certain dates occur for our members, such as 65th birthday for example. However when I do my calculations and then cast the result to a date using the to_date function I get a weird result I get the correct day and month, but the year is wrong. For example if a members 65th birthday was 28/09/2006, it appears as 28/09/0006. Does anyone have an explaination why it does this? When I use the to_char function with 'dd/mm/yyyy' as the format mask things are fine.

Lauren McDonald, B.Sc.
Programmer Analyst
Saskatchewan Healthcare Employees' Pension Plan

 

[SantaMufasa]

Lauren,

My crystal ball is a bit hazy today, so I'll need you to post both your code that is producing the erroneous result and the code that is producing the correct result.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[LaurenM]

select
TO_DATE(X.BIRTHDATE + (65 * 365.25)) + 1, 'DD/MM/YYYY') AS NRD -- gives 28/09/0006
TO_CHAR(X.BIRTHDATE + (65 * 365.25)) + 1, 'DD/MM/YYYY') AS NRD2 -- gives 28/09/2006
from
member x

Lauren McDonald, B.Sc.
Programmer Analyst
Saskatchewan Healthcare Employees' Pension Plan

 

[SantaMufasa]

Lauren,

"BIRTHDATE" (I presume) is already a DATE expression. Why are you running a DATE through a TO_DATE function?

Here is the Oracle rational for your receiving a bogus representation in the first expression is this:

1) Oracle processes expressions from the innermost parentheses outward, then from left to right:
a) 65 * 365.25 = 23741.25
b) BIRTHDATE ("27-SEP-1941") + 23741.25 = 27-SEP-2006
c) 27-SEP-2006 + 1 = 28-SEP-2006

2) By your using the TO_DATE function, Oracle presumes that you are trying to convert a character string into a DATE expression. But since BIRTHDATE is already a DATE, and your current intermediate expression value is a DATE (28-SEP-2006), Oracle says to itself, "I must convert this date into a string to perform this function."

3) So, Oracle converts the DATE, 28-SEP-2006, from its internal DATE representation into string characters. Oracle's default character format for dates is DD-MON-YY. In your case, the result is "20-SEP-06".

4) Next, Oracle takes that character default format and applies your mask, 'DD/MM/YYYY'. Since your year value is "06", Oracle can only presume that the year is 6 A.D. Thus, your resulting DATE is "28/09/0006".

So, your error resulted from your incorrectly applying an extraneous CHARACTER-to-DATE conversion.

Let us know if this explains your situation to your satisfaction.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[LaurenM]

So, if I took off the to_date function then the result would be of type DATE?

Lauren McDonald, B.Sc.
Programmer Analyst
Saskatchewan Healthcare Employees' Pension Plan

 

[SantaMufasa]

Yep.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[LaurenM]

Thanks Santa!

Lauren McDonald, B.Sc.
Programmer Analyst
Saskatchewan Healthcare Employees' Pension Plan

 

[Beilstwh]

Your calculation is also incorrect. use the following.

select
add_months(X.BIRTHDATE,65 * 12)) + 1 AS NRD
from
member x

Bill
Oracle DBA/Developer
New York State, USA

 

[LaurenM]

Actually the calculation is last_day((x.birthdate + (65 + 365.25))) + 1, since our plan says thats when a members' normal retirement date is. It is on the first of the month of the month following the members' 65th birthday. And the 365.25 is correct, since that is actually how many days there are in a year, hence the reason for a leap year every four years.

Lauren McDonald, B.Sc.
Programmer Analyst
Saskatchewan Healthcare Employees' Pension Plan

 

[SantaMufasa]

True, Lauren (365.25 calculates a year correctly, over a 4-year period of time), but Bill is correct as well: "add_months(X.BIRTHDATE,65 * 12)) + 1" produces the day following ones 65th birthday...and that calculation includes leap-year adjustments. (I, personally, advocate Bill's calculation over a "365.25" calculation.)

So, your revised alternative to your original code could be:

...last_day(add_months(X.BIRTHDATE,65 * 12)) + 1 AS NRD...

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]