0

I am trying to insert/update DATE_OF_RETIREMENT column, but when the year is >=2050, it takes as 1950.

Example:

UPDATE EMPLOYEE
SET STATUS             = '4',
    ISACTIVE           = 1,
    DATE_OF_RETIREMENT = '30-APR-49'
WHERE ID = 2001;

Here DATE_OF_RETIREMENT is updated with 30 APR 2049.

But in following case:

UPDATE EMPLOYEE
SET STATUS             = '4',
    ISACTIVE           = 1,
    DATE_OF_RETIREMENT = '30-APR-52'
WHERE ID = 2001;

Here DATE_OF_RETIREMENT with updated with 30 APR 1952.

Can anyone tell me the reason why this is happening?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Jimmy
  • 995
  • 9
  • 18
  • 5
    Any reason you don't use four digit years to avoid this problem once and for all? With two digit years obviously *some* dates are inevitably going to end up wrong no matter what window is used. If it's not dates after 2050 it'll be dates after 2060, and if it's not those than eventually the 2100s will end up wrong. The database must make a decision as to what century is meant if only 2 digits are present, and it won't decide correctly for all cases. – Jeroen Mostert May 17 '21 at 15:29
  • When you don't specify a format or a date language, the database has to fall back on defaults. It looks like your `nls_date_format` is `'DD-MON-RR'` and your `nls_date_language` is English. How should it know what century you mean by `52`? – William Robertson May 17 '21 at 22:34
  • Thanks @Jeroen ! Actually it's a legacy system which was working fine up to now but some such issues is raised few days earlier. Now I have take complete 4-digits of years in *date_of_retirement* – Jimmy May 18 '21 at 06:45

2 Answers2

3

Use 4 digits for years; I hope you aren't trying to save some disk space, are you? Y2K is 20 years behind us.

See the difference between RR and YY format mask for years. Depending on whether it (year) is before or after the 50th year in the century, you'll get a different result.

SQL> alter session set nls_date_format = 'dd.mm.yyyy';

Session altered.

SQL> select to_date('30-04-49', 'dd.mm-rr') val1_rr,
  2         to_date('30-04-49', 'dd.mm-yy') val2_yy,
  3         --
  4         to_date('30-04-52', 'dd.mm-rr') val3_rr,
  5         to_date('30-04-52', 'dd.mm-yy') val4_yy
  6  from dual;

VAL1_RR    VAL2_YY    VAL3_RR    VAL4_YY
---------- ---------- ---------- ----------
30.04.2049 30.04.2049 30.04.1952 30.04.2052

SQL>
Littlefoot
  • 131,892
  • 15
  • 35
  • 57
  • 3
    In fairness, the admonition should be addressed to Oracle first. The default date format model, right out of the box, even if I install the database today, is `dd-MON-rr`. Can't fault beginners for assuming that what's good for the default provided by Oracle itself is also good for the user. –  May 17 '21 at 15:38
  • 1
    Thank you for the comment, @mathguy. As usual, I enjoy reading them, as well as your answers. – Littlefoot May 17 '21 at 15:41
0

The reason this is happening is because Oracle has a built-in century cutoff date, and the default is 50. Anything less than or equal to the cut-off year is considered to fall in the current century.

Neville Kuyt
  • 29,247
  • 1
  • 37
  • 52