4

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.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
SarthAk
  • 1,628
  • 3
  • 19
  • 24

3 Answers3

8

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):

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:

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:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
0

Here is a sample function I wrote to solve this issue. I myself was working on same migration. Hope it help u someway. You may do it using function overloading though, just rename the function as u like.

CREATE OR REPLACE FUNCTION to_date_rr(TEXT, TEXT)
  RETURNS DATE AS
  $$
      DECLARE
        date_v DATE;
        fmt text := upper($2);
        DATE_VALUE TEXT :=$1;
        digit_diff numeric := length($1) - length($2);

      BEGIN

    $2 = upper($2);

    IF substring(fmt from position('RRRR' in fmt) for 4) = 'RRRR' THEN

        IF digit_diff < 0 THEN
            fmt := replace($2, 'RRRR', 'YYYY');
            IF substring(DATE_VALUE from position('RRRR' in $2) for 2) > '50' THEN
                date_v := to_date(overlay(DATE_VALUE placing '19' from position('RRRR' in $2) for 0), fmt);
            ELSE
                date_v := to_date(overlay(DATE_VALUE placing '20' from position('RRRR' in $2) for 0), fmt);
            END IF;
        ELSE
            fmt := replace($2, 'RRRR', 'YYYY');
            date_v := to_date($1, fmt);
        END IF;

    ELSIF substring(fmt from position('RR' in fmt) for 2) = 'RR' THEN
        IF digit_diff = 0 THEN
            fmt := replace($2, 'RR', 'YY');         
            IF substring(DATE_VALUE from position('RR' in $2) for 2) > '50' THEN
                date_v := to_date(overlay(DATE_VALUE placing '19' from position('RR' in $2) for 0), fmt);
            ELSE
                date_v := to_date(overlay(DATE_VALUE placing '20' from position('RR' in $2) for 0), fmt);
            END IF;


        ELSE
            fmt := replace($2, 'RR', 'YY');
            date_v := to_date($1, fmt);

        END IF;


    ELSIF substring(fmt from position('YY' in fmt) for 2) = 'YY' and substring(fmt from position('YYYY' in fmt) for 4) != 'YYYY' THEN
        IF digit_diff = 0 THEN  
            IF substring(DATE_VALUE from position('YY' in $2) for 2) >= '00' THEN
                date_v := to_date(overlay(DATE_VALUE placing '20' from position('YY' in $2) for 0), fmt);

            END IF;

        ELSIF digit_diff < 0 THEN
            IF substring(DATE_VALUE from position('YY' in $2) for 2) >= '00' THEN
                date_v := to_date(overlay(DATE_VALUE placing '200' from position('YY' in $2) for 0), fmt);      
            END IF;
        ELSE
            date_v := to_date($1, fmt);
        END IF;

    ELSE
        SELECT to_date($1, $2) INTO date_v;

    END IF;


        RETURN date_v;

      END;
  $$
LANGUAGE plpgsql;
Amir Hossain
  • 173
  • 2
  • 14
0

It can be done using to_char(Date Field, 'format to display')

Chhaya Sethi
  • 1