| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: strange function result
it looks like someone has changed the default database date mask from dd-mon-yy to dd-mon-rr. This would make the dates with years <50 be in the next century, not in this one. For example, consider:
SQL> alter session set nls_date_format = 'dd-mon-yy';
Session altered.
SQL> select trunc( months_between( sysdate, '20-jun-31')/12 ) from dual;
TRUNC(MONTHS_BETWEEN(SYSDATE,'20-JUN-31')/12)
65
SQL> alter session set nls_date_format='dd-mon-rr';
Session altered.
SQL> select trunc( months_between( sysdate, '20-jun-31')/12 ) from dual;
TRUNC(MONTHS_BETWEEN(SYSDATE,'20-JUN-31')/12)
-34
SQL> So, when the default nls_date_format is set to YY, you get 65 years old, when set to RR, you see it will be 34 years until the person is born.
To verify the dates in your database, "select to_char(bdate, 'dd-mon-yyyy') from employee where bdate > sysdate;". That will retrieve the records with dates beyond today (people not yet born). Perhaps someone loaded the character string 'dd-mon-yy' with a format of 'dd-mon-rr' which would mess up the dates before 1950; or you are converting the valid date to a string and having the RR format applied to it...
On 9 Feb 1997 04:03:26 GMT, medlocrf_at_dunx1.ocs.drexel.edu (Royce Medlock) wrote:
>I have created the following function:
>
>create or replace function AGE(a_bdate employee.bdate%type)
>return INT IS
>age INT;
>Begin
>select trunc(MONTHS_BETWEEN(SYSDATE, BDATE)/12) into age
>from employee
>where bdate = a_bdate;
>RETURN age;
>END AGE;
>
>here is some sample data I get when I use it:
>
>BDATE AGE
>--------- ----------
>09-JAN-55 42
>15-SEP-52 44
>19-JUL-58 38
>20-JUN-31 -34
>08-DEC-45 -48
>31-JUL-62 34
>29-MAR-59 37
>10-NOV-27 -30
>10-OCT-65 31
>19-DEC-70 26
>28-JAN-03 -5
>
>The problem calculates properly for the more recent birthdates, but later
>birthdates return a negative value. I explored the MONTHS_BETWEEN
>function and it returns INT type which is at least 2 bytes so I shouldn't
>be exceeding the range because i'm not close to 32,000. I know it can't
>be 1 byte because ex. 2 with age of 44 equals a MONTHS_BETWEEN of 528,
>which would be out of a 1 byte range and it works fine for it.
>
>If anyone has any ideas as to what my problem might be please drop me a
>line. Any help is appreciated.
>
>Thanks in advance,
>
>Frankie Medlock
>medlocrf_at_dunx1.ocs.drexel.edu
>
Thomas Kyte
Oracle Government
tkyte_at_us.oracle.com
 |
 |