Convert Excel column date with different patterns - Oracle PLSQL

Question

I'm trying to convert the column DATA_EMISSAO which has a char format but contains all dates for the Table (it only contains dates).

The problem is: the way information was loaded into Oracle let the row set with different patterns as shown below:

As you can see there are 2 patterns: 'DD/MM/YYYY' and Excel's Date number format.

1) First I tried to convert them with the code:

  select distinct to_char(DATA_EMISSAO, 'DD-MM-YYYY') from MASTERSAF;

But got the error:

  ORA-01722: invalid number
  01722. 00000 -  "invalid number"
  *Cause:    The specified number was invalid.
  *Action:   Specify a valid number.

2) Then I tried the following:

  select distinct to_char(to_date(DATA_EMISSAO, 'DD-MM-YYYY'), 'DD-MM-YYYY') from MASTERSAF;

But got no success...

   ORA-01847: day of month must be between 1 and last day of month
   01847. 00000 -  "day of month must be between 1 and last day of month"
   *Cause:    
   *Action:  

Do you think it should be better to construct a condition to read each row condition and apply the proper "transformation"?

Also, I don't know why, but if I run

   select to_char(to_date(DATA_EMISSAO, 'DD/MM/YYYY') + 0, 'DD/MM/YYYY') as DATES2 from MASTERSAF;

It seems to go fine, but if I put a distinct before,

                select distinct to_char(to_date(DATA_EMISSAO, 'DD/MM/YYYY') + 0, 'DD/MM/YYYY') as DATES2 from MASTERSAF;

I get the following error:

    ORA-01847: day of month must be between 1 and last day of month
    01847. 00000 -  "day of month must be between 1 and last day of month"
    *Cause:    
    *Action:

Do you have an idea to correct the dates? (unfortunately I cannot change on excel because the database is too large....)

Answer 1

Use a CASE statement to match the different formats and convert them each to a date using the appropriate method (DD/MM/YYYY can be converted using TO_DATE and a single number can be converted by adding days to 1900-01-01):

Oracle Setup:

CREATE TABLE dates ( date_emissao ) AS
SELECT '12/08/2016' FROM DUAL UNION ALL
SELECT '42716' FROM DUAL

Query:

SELECT CASE
       WHEN REGEXP_LIKE( date_emissao, '^\d+$' )
       THEN DATE '1900-01-01' + TO_NUMBER( date_emissao )
       ELSE TO_DATE( date_emissao, 'DD/MM/YYYY' )
       END AS date_emissao
FROM   dates;

Output:

| DATE_EMISSAO |
| :----------- |
| 2016-08-12   |
| 2016-12-14   |

db<>fiddle here