I have a column in an Oracle table which is varchar and it contains different date formats like ‘dd-mom-yyyy’, ‘dd-mm-yyyy’, ‘dd/mm/yy’. I need to transform all these formats into a single one, keeping the column as varchar.

CodePudding user response：

As already commented, idea of storing dates as strings is bad. It always is.

You said you want to keep values as they are; it means that you'd actually just want to display values as valid dates. To do that, one option is to create your own function which accepts various values, tries to convert them to date and return it (if it succeeds).

You'd add other formats into the function, once you find them.

For example:

SQL> create or replace function f_format (par_datum in varchar2) return date is
  2  retval date;
  3  begin
  4    -- DD-MON-YYYY
  5    begin
  6      retval := to_date(par_datum, 'dd-mon-yyyy', 'nls_date_language = english');
  7    exception
  8      when others then
  9
 10        -- DD-MM-YYYY
 11        begin
 12          retval := to_date(par_datum, 'dd-mm-yyyy');
 13        exception
 14          when others then
 15
 16            -- DD/MM/YYYY
 17            begin
 18              retval := to_date(par_datum, 'dd/mm/yyyy');
 19            exception
 20              when others then
 21                null;
 22            end;
 23        end;
 24    end;
 25
 26    return retval;
 27  end;
 28  /

Function created.

Let's try it (setting date format, just to know what is what):

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

Session altered.

varchar2 column accepts even garbage; 45-19-2001 looks like date, but it is not.

SQL> select id, datum, f_format(datum) result
  2  from test
  3  order by id;

        ID DATUM                RESULT
---------- -------------------- ----------
         1 25-OCT-1998          25.10.1998
         2 13-02-2022           13.02.2022
         3 03/11/1984           03.11.1984
         4 45-19-2001

SQL>

CodePudding user response：

From Oracle 12, you can use TO_DATE with the DEFAULT NULL ON CONVERSION ERROR option to convert the string to a date against a known format and TO_CHAR to convert it back to a string and use COALESCE to check multiple formats:

UPDATE table_name
SET date_column = COALESCE(
                    TO_CHAR(
                      COALESCE(
                        TO_DATE(date_column DEFAULT NULL ON CONVERSION ERROR, 'fxDD-MM-RR'),
                        TO_DATE(date_column DEFAULT NULL ON CONVERSION ERROR, 'DD-Mon-YYYY', 'NLS_DATE_LANGUAGE=English'),
                        TO_DATE(date_column DEFAULT NULL ON CONVERSION ERROR, 'YYYY-MM-DD'),
                        TO_DATE(date_column DEFAULT NULL ON CONVERSION ERROR, 'MM-DD-YYYY')
                      ),
                      'YYYY-MM-DD'
                    ),
                    date_column
                  )

Then, for the sample data:

CREATE TABLE table_name (date_column) AS
  SELECT '2023-01-25' FROM DUAL UNION ALL
  SELECT '01-25-2023' FROM DUAL UNION ALL
  SELECT '25-01-23' FROM DUAL UNION ALL
  SELECT '25-Jan-2023' FROM DUAL UNION ALL
  SELECT 'Wed 25th January 2023' FROM DUAL

After the update, the table contains:

fiddle

Note: This normalises all the date formats that are tested in the UPDATE statement and will leave the non-matching formats as they were (so you will not lose data).

Even better would be to store the values as a DATE data type and not a string.