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