0

I have a string field in database named "ExamDate" which contains dates. Initially, the application had no front-end validation in place and database was saving as string, so users were able to input dates in any format.

The required format is DD-MMM-YYYY but currently the field has values in all formats, like:

  1. 14-Jun-2017
  2. 9/15/2017
  3. May 2017
  4. February 1, 2017
  5. NULL value

I have to display this field in a DB view and convert all of them into date format.

The various options I tried give me errors like: "a non-numeric character was found where a numeric was expected" or "invalid number"

I also realized that the "day" field is missing in a few of the dates, like 'May 2017' which needs to be set to 01-May-2017.

Do you suggest going ahead with a solution similar to one pasted below from path: How to update dates stored as varying character formats (PL/SQL)?

SELECT ANTICIPATEDSPUD
      ,DECODE (
           INSTR (ANTICIPATEDSPUD, '-')
          ,5, TO_DATE (ANTICIPATEDSPUD, 'YYYY-MM-DD')
          ,3, TO_DATE (ANTICIPATEDSPUD, 'MM-DD-YYYY')
          ,DECODE (LENGTH (ANTICIPATEDSPUD)
                  ,8, TO_DATE (ANTICIPATEDSPUD, 'MM/DD/YY')
                  ,10, TO_DATE (ANTICIPATEDSPUD, 'MM/DD/YYYY')))
  FROM FSW_BASIC_WELL_INFO_VW;
William Robertson
  • 15,273
  • 4
  • 38
  • 44
user1630575
  • 171
  • 1
  • 15
  • 1
    Possible duplicate of [Convert multiple date varchar2 to date format in Oracle 11g](https://stackoverflow.com/questions/31681941/convert-multiple-date-varchar2-to-date-format-in-oracle-11g) – clinomaniac Jan 26 '18 at 23:50
  • Are you looking for a pure SQL solution, or can you create a function? (If you are on Oracle 12.1 or later you can define a PL/SQL function inline.) If SQL only, I find `case` more readable and flexible than `decode`. – William Robertson Jan 27 '18 at 12:08
  • I've added a pure SQL solution for Oracle 12.2 over on [Oracle Date Column cleaning](https://stackoverflow.com/a/48476588/230471). – William Robertson Jan 27 '18 at 14:08

1 Answers1

1

Perhaps you could try to group data that share the same format. REGEXP_LIKE might be handy in such a case. It might be OK as it allows you to "upgrade" it easily, as soon as you find yet another format people used to enter data.

The following example certainly isn't ideal because of typos; months could have been "Ferubrary" or "Mya"; days could be 35, months 13 and so forth so you'd have to check whether those values are meaningful.

Anyway; have a look. I've included the ID column just for sorting purposes.

SQL> alter session set nls_date_language = 'english';

Session altered.

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

Session altered.

SQL> with test (id, datum) as
  2  (select 1, '14-Jun-2017'      from dual union
  3   select 2, '9/15/2017'        from dual union
  4   select 3, 'May 2017'         from dual union
  5   select 4, 'February 1, 2017' from dual union
  6   select 5, null               from dual
  7  )
  8  select id, datum, to_date(datum, 'dd-mon-yyyy') result from test
  9    where regexp_like(datum, '[0-9]{1,2}-[[:alpha:]]{3}-[0-9]{4}')
 10  union
 11  select id, datum, to_date(datum, 'mm/dd/yyyy') from test
 12    where regexp_like(datum, '[0-9]{1,2}/[0-9]{1,2}/[0-9]{4}')
 13  union
 14  select id, datum, to_date(datum, 'mon yyyy') from test
 15    where regexp_like(datum, '[[:alpha:]]{3} [0-9]{4}')
 16  union
 17  select id, datum, to_date(datum, 'month dd, yyyy') from test
 18    where regexp_like(datum, '\w+ [0-9]{1,2}, [0-9]{4}')
 19  order by id;

        ID DATUM            RESULT
---------- ---------------- ----------
         1 14-Jun-2017      14.06.2017
         2 9/15/2017        15.09.2017
         3 May 2017         01.05.2017
         4 February 1, 2017 01.02.2017

SQL>
Littlefoot
  • 131,892
  • 15
  • 35
  • 57