Why does Oracle not recognize standard date formats automatically? Is there something I am missing?
If a date is (14-FEB-23) or (2/14/23) it is the same date. Forgive me, but in Microsoft products, if the data coming in from the source is either format, Microsoft recognizes it as a date and works with it. However, it does not seem so with Oracle.
I have a data source that comes from an Oracle reporting system to which I have no access. It is an FTP transferred CSV file.
Before I get the advice to fix the source, I cannot fix source. I have to import the data and it has date values (coming from Oracle) that are of both formats.
Given that it is a CSV file, we import it into a staging table as string characters. Then, when we want to transform it, we want to use the TO_DATE function. The problem is that TO_DATE requires you to specify the exact date format (DD-MON-YY) or (MM/DD/YY) and anything that does not meet that criteria is considered an invalid date.
Is there some secret handshake to get Oracle SQL to accept multiple date formats as inputs and return a DATE value?
In MSSQL we would write something like:
SELECT FORMAT([DUE_DATE],'YYYY-MM-DD') DueDate FROM MySource
This would take any valid date format and convert it to the 2023-02-14 format. I could then CAST it to another data type, including DATE or DATETIME. The key being, [DUE_DATE]does not have to be defined. It simply has to be a valid date format.
Imagine a dataset like this:
WITH DATA AS
(
SELECT '2/14/23' DUE FROM DUAL
UNION ALL
SELECT '2/14/2023' DUE FROM DUAL
UNION ALL
SELECT '14-FEB-23' DUE FROM DUAL
UNION ALL
SELECT '14-FEB-2023' DUE FROM DUAL
UNION ALL
SELECT '2023-02-14' DUE FROM DUAL
)
SELECT * FROM DATA
Is there a way to use TO_DATE or similar to take these standard date formats and convert them to a DATE value?