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

CodePudding user response：

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).

CodePudding user response：

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