Mysterious change of year from 2050 to 1950

Question

There is something unusual going on. While migration, I have some dates beyond 2050 (for example, 20.05.2050, 21.11, 2051) but for some reason, Oracle changes them to 1950, 1951 etc. Quite annoying if you ask me and it was reported by customer. These are retirement dates so obviously they can't be in 1950s and 1960s

The date in original table is like YYYYMMDD format aka 20500130. So here is my merge statement

MERGE INTO employment_data emp                                                     
   USING   temp_02 src
   ON      (TO_NUMBER(src.id) = emp.id)
   WHEN MATCHED THEN UPDATE SET 
       emp.retirement_day = DECODE(TO_NUMBER(src.retirement), 0, NULL, TO_DATE(src.retirement, 'YYYY.MM.DD'));

Other dates are ok (e.g 20301204) but any dates after 2050 goes nuts. Any idea how may I solve this annoyance?

Thx in advance :-)

Possible explanation here http://stackoverflow.com/questions/16847010/different-dates-oracle-11g-with-toad — Noel, May 31 '13 at 14:45
what is the format? you mention YYYYMMDD (20500130), and then use YYYY.MM.DD, and also mention example of (04122030), which is MMDDYYYY I suppose. — tbone, May 31 '13 at 14:50
Yes, something doesn't add up. If you call `TO_NUMBER()` on a value like `2050.05.20` you'll get an invalid number exception, so the first condition of the `DECODE` should throw for non-zero dates. — Ed Gibbs, May 31 '13 at 14:57
Now, here is the funny part. If I run SELECT TO_DATE(retirement, 'YYYY.MM.DD') from temp_02 query then I get correct results. But if I do the same without decode in merge query, insert data in db and run select query for checking then I see 1950s being inserted in db. — Jaanna, May 31 '13 at 15:43

score 9 · Accepted Answer · edited Jun 20 '20 at 09:12

The problem is that the DECODE forces an implicit conversion. The return datatype of the DECODE function is determined by the rule:

DECODE(expr, search, result [, search, result]* [, default])
Oracle automatically converts expr and each search value to the data type of the first search value before comparing. Oracle automatically converts the return value to the same data type as the first result. If the first result has the data type CHAR or if the first result is null, then Oracle converts the return value to the data type VARCHAR2.

In Oracle NULL has the default datatype of VARCHAR2 so your DECODE expression is equivalent to:

DECODE(TO_NUMBER(src.retirement), 0, 
       NULL, 
       TO_CHAR(TO_DATE(src.retirement, 'YYYY.MM.DD')));

Without format, the TO_CHAR uses your NLS_DATE_FORMAT session setting, probably the default DD-MON-RR which loses the century information.

As noted in this related question, the rules for RR are as follow:

If the specified two-digit year is 00 to 49, then

If the last two digits of the current year are 00 to 49, then the returned year has the same first two digits as the current year.

If the last two digits of the current year are 50 to 99, then the first 2 digits of the returned year are 1 greater than the first 2 digits of the current year.

If the specified two-digit year is 50 to 99, then

If the last two digits of the current year are 00 to 49, then the first 2 digits of the returned year are 1 less than the first 2 digits of the current year.

If the last two digits of the current year are 50 to 99, then the returned year has the same first two digits as the current year.

This is why dates before 2050 had not triggered the mysterious behaviour!

score 1 · Answer 2 · answered Jun 03 '13 at 07:16

I have figured out that way and thought it may help others.

I am not sure if it is the problem with Oracle settings or some naughtiness from PL/SQL Developer. In any case, I checked the the NLS_DATE_FORMAT first.

SELECT value FROM v$nls_parameters
WHERE parameter = 'NLS_DATE_FORMAT'

which showed that the it is set to DD-MON-RR.

Now without changing the configuration, I'd like to change the setting for the migration session and for that session only.

Alter SESSION SET nls_date_format = 'dd-mon-yyyy';

Then run the merge statement.

MERGE INTO employment_data emp                                                     
   USING   temp_02 src
   ON      (TO_NUMBER(src.id) = emp.id)
   WHEN MATCHED THEN UPDATE SET 
       emp.retirement_day = TO_CHAR(TO_DATE(src.retirement,'YYYY.MM.DD'));

and this worked.

Couple of rules for you: 1. Never store or manipulate dates as anything other than a DATE data type. 2. Never rely on implicit date conversions -- you should always specify a date format in any conversions. — David Aldridge, Jun 03 '13 at 07:35

Mysterious change of year from 2050 to 1950

2 Answers2