'DD-MON-RR' date format pattern not working as expected

Question

I am inserting a query with the format as

to_date('25-JUN-13','DD-MON-RR')

In oracle it is working fine and the output is as 25-JUN-13.
In postgresql the same is working as 0001-06-25 BC.

It is a migration project from the oracle database to postgresql. Any solution for the same to work as it is in the case of oracle.

The same is not working correctly if I am using the DD-MM-YY format then the result is being very much different.

RUNNING THIS QUERY IN POSTGRESQL -->

select to_char(to_date('25-JUN-53','DD-MON-YY'),'YYYY') as YEAR 

ANSWER IS --> 2053 

While retrieving the same result in oracle from the query as

select to_char(to_date('25-JUN-53','DD-MON-RR'),'YYYY') as YEAR from dual

ASSWER IS --> 1953

As I am migrating the project the same functionality should be there in the Postgresql so that the final result should be same.

Answer 1

The same is not working correctly ...

Of course it is working correctly. The manual:

If the year format specification is less than four digits, e.g. YYY, and the supplied year is less than four digits, the year will be adjusted to be nearest to the year 2020, e.g. 95 becomes 1995.

So, 70 becomes 1970, but 69 becomes 2069.

Oracle has different rules for the format specifier RR, which does not exist in Postgres. Basically, the year will be adjusted to be nearest to the year 2000 (the nearest century to the current date):

• https://docs.oracle.com/cd/B28359_01/server.111/b28286/sql_elements004.htm#SQLRF00216

Workaround

Encapsulate the functionality in a function that switches the century according to the year number in the string. Since Postgres allows function overloading, you can even use the same function name to_date() with different parameter types. See:

• ERROR: function addgeometrycolumn is not unique

According to the documentation above, Oracle wraps around at YY = '50' and this function is equivalent until 2049:

CREATE OR REPLACE FUNCTION to_date(varchar, text)
  RETURNS date
  LANGUAGE sql STABLE AS
$func$
SELECT CASE WHEN right($1, 2) > '49' THEN
         to_date(left($1, -2) || '19' || right($1, 2), 'DD-MON-YYYY')
      ELSE
         to_date(left($1, -2) || '20' || right($1, 2), 'DD-MON-YYYY')
      END
$func$;

Only STABLE, not IMMUTABLE, because to_date is only STABLE. Else you disable function inlining.

I chose varchar for the first parameter to be different from the original, which uses text.
If the year number is > 49, the function adds the 20th century (with '19') else, the 21st into the date string before conversion. The second parameter is ignored.

Call:

SELECT to_date('25-JUN-53'         , 'DD-MON-YY') AS original
     , to_date('25-JUN-53'::varchar, 'DD-MON-YY') AS patched1
     , to_date('25-JUN-53'::varchar, 'DD-MON-RR') AS patched2
     , to_date('25-JUN-53'::varchar, 'FOO-BAR')   AS patched3

Our custom function ignores the 2nd parameter anyway.

Result:

  original  |  patched1  |  patched2  |  patched3  
------------+------------+------------+------------
 2053-06-25 | 1953-06-25 | 1953-06-25 | 1953-06-25

db<>fiddle here
_{Old sqlddle}

You might make it more sophisticated to work beyond 2049 and take the second parameter into consideration ...

---

A word of warning: function overloading over basic functions is better done with care. If that stays in your system somebody might get surprising results later.

Better create that function in a special schema and set the search_path selectively so it only gets used when appropriate. You can as well use text as parameter type in this case:

CREATE SCHEMA specialfunc;
CREATE OR REPLACE FUNCTION specialfunc.to_date(text, text) AS ...

Then:

SET search_path = specialfunc, pg_catalog;
SELECT to_date('25-JUN-53', 'DD-MON-YY') AS patched;

Or use a temporary function. See:

• How to create a temporary function in PostgreSQL?