Transform differents dates format to one format - Oracle SQL

Question

I have a column with two date format :

1. DD/MM/YYYY
2. YYYY-MM-DD

I want to transform all date format of this column to only one date format, it is 'DD/MM/YYYY'

How can i do it in Oracle SQL ?

Thanks in advance

Answer 1

NEVER store dates as strings; always use a DATE or TIMESTAMP data type.

---

From Oracle 12, you can use multiple calls to the TO_DATE function with different format models and use DEFAULT NULL ON CONVERSION ERROR to prevent errors being raised and COALESCE them to find the match:

SELECT date_string_column,
       COALESCE(
         TO_DATE(
           date_string_column DEFAULT NULL ON CONVERSION ERROR,
           'fxDD/MM/YYYY'
         ),
         TO_DATE(
           date_string_column DEFAULT NULL ON CONVERSION ERROR,
           'fxYYYY-MM-DD'
         )
       ) AS date_value
FROM   table_name

Which, for the sample data:

CREATE TABLE table_name (date_string_column) AS
SELECT '2021-01-01' FROM DUAL UNION ALL
SELECT '31/12/2022' FROM DUAL;

Outputs:

DATE_STRING_COLUMN	DATE_VALUE
2021-01-01	2021-01-01 00:00:00
31/12/2022	2022-12-31 00:00:00

db<>fiddle here

Answer 2

Assuming you only have those 2 formats, you can do something like:

select (case when instr(columnName, '/') > 1 then 
             to_date(columnName, 'DD/MM/YYYY') 
        else to_date(columnName, 'YYYY-MM-DD') 
        end) field_as_date 
 from tableName;

This should give you columnName as a Date (without the time part ofc).

Answer 3

Here is another way.

String data is horrible for storing anything important. According to one of the database I work with, there are over 4000 counties in Washington State. There are also about 15 ways to spell "3M". You'll probably also need to find ways to cleanse your data or handle non-date data in that column.

with dt (dt) as (
  select cast('2021-01-31' as varchar2(50))
  from dual
  union select cast('31/01/2021' as varchar2(50))
  from dual
)

select 
  dt
, to_date(
    regexp_replace(
      regexp_replace(
        dt
        , '([[:digit:]]{2})/([[:digit:]]{2})/([[:digit:]]{4})'
        , '\3-\2-\1'
      )
      , '([[:digit:]]{4})-([[:digit:]]{2})-([[:digit:]]{2})'
      , '\1-\2-\3'
    )
    , 'yyyy-mm-dd'
  ) "dt"
from dt;