Incorrect update in Oracle SQL DB

Question

I tried to update a lot of rows in date format ('DD-MON-YY'). S

So, in cases such 22-FEB-99, I got 22th February 1999, instead of 22th February 2099.

Is there a way to specify the year in such cases?

Answer 1

I tried to update a lot of rows in date format ('DD-MON-YY').

If you have a DATE column then that is a binary data type consisting of 7 bytes (representing century, year-of-century, month, day, hour, minute and second); it ALWAYS has those components and it is NEVER stored in any (human-readable) format.

What you probably mean is that your client application (SQL Developer) is displaying the dates in the format DD-MON-RR and it is not showing you the century or time components.

---

Is there a way to specify the year in such cases?

Rather than letting the client application apply a default format for displaying the date, you can apply an explicit format using TO_CHAR:

SELECT TO_CHAR(your_date_column, 'FMDDth Month YYYY', 'NLS_DATE_LANGUAGE=English')
         AS formatted_date
FROM   your_table;

Which, for the sample data:

CREATE TABLE your_table (your_date_column) AS
SELECT SYSDATE FROM DUAL UNION ALL
SELECT DATE '2022-01-01' FROM DUAL UNION ALL
SELECT DATE '1970-01-01' FROM DUAL;

Outputs:

FORMATTED_DATE
2nd November 2022
1st January 2022
1st January 1970

---

in cases such 22-FEB-99, I got 22th February 1999, instead of 22th February 2099.

If you get the value 22th February 1999 then that is because the date is stored as 1999 and not 2099. Note: that a DATE data type always has a century component so the query will display what is stored in the column.

You have probably inserted (or updated) the date as a string:

INSERT INTO your_table (your_date_column)
VALUES('22-FEB-99');

'22-02-99' is not a date data type, it is a string literal. Oracle has tried to be helpful and implicitly convert the date to a string using the NLS_DATE_FORMAT session parameter effectively converting the query to:

INSERT INTO your_table (your_date_column)
VALUES(
  TO_DATE(
    '22-FEB-99',
     (SELECT value FROM NLS_SESSION_PARAMETERS WHERE parameter = 'NLS_DATE_FORMAT')
  )
);

However, your NLS_DATE_FORMAT session parameter is set to DD-MON-RR and the RR format model will convert 99 to 1999 and not 2099.

What you need to do is NEVER use implicit conversion from strings to dates; instead:

• Use a date literal:

  INSERT INTO your_table(your_date_column) VALUES (DATE '2099-02-22');
  

• Use a timestamp literal (which also allows you to specify the time):

  INSERT INTO your_table(your_date_column) VALUES (TIMESTAMP '2099-02-22 00:00:00');
  

• Explicitly convert the string using TO_CHAR and the correct format model:

  INSERT INTO your_table(your_date_column)
  VALUES (TO_DATE('22-FEB-99', 'DD-MON-YY', 'NLS_DATE_LANGUAGE=English'));
  

fiddle

---

Fixing invalid data

If you have the data as 1999 and want 2099 then you will need to fix it:

UPDATE your_table
SET    your_date_column = your_date_column + INTERVAL '100' YEAR(3)
WHERE  your_date_column = DATE '1999-02-22';

or:

UPDATE your_table
SET    your_date_column = ADD_MONTHS(your_date_column, 1200)
WHERE  your_date_column = DATE '1999-02-22';

or:

UPDATE your_table
SET    your_date_column = DATE '2099-02-22'
WHERE  your_date_column = DATE '1999-02-22';