CODEWITHSUNDEEP
oracle-databasedatevarchar
Wael Q
Wael Q

Reputation: 13

Oracle Varchar2 column with multiple date formats

I have a table (my table ) with a varchar2 column (my_column) , this column inculdes data that may be dates, in almost every known date format. like dd/mm/yyyy mm/dd/yyyy ddMONyyyy ddmmyyyyy yymmdd mm.dd.yy dd/mm/yyyy hh:24:ss mm-dd-yyyy and so many . Also it could contains a free text data that may be numbers or text. I need the possible fastest way to retrieve it as a date , and if it's cannot match any date format to retrieve null

Upvotes: 0

Views: 859

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

The simple answer is: You cannot convert the strings to dates.

You say that possible formats include 'dd/mm/yyyy' and 'mm/dd/yyyy'. So what date would '01/02/2000' be? February 1 or January 2? You cannot know this, so a conversion is impossible.

Having said that, you may want to write a stored procedure where you parse the text yourself and only convert unambiguous strings.

E.g.: Is there a substring containing colons? Then this may be the time part. In the rest of the string: Is there a four-digit number? Then this may be the year. And so on until you are either 100% sure what date/time this is or you cannot unambiguously determine the date (in which case you want to return null).

Example: '13/01/21' looks very much like January 13, 2021. Or do you allow the year 2013 in your table or even 2001 or even 1921 or ...? If you know there can be no year less then, say, 2019 in the table and no year greater than 2021, then the year must be 2021. 13 cannot be the month, so it must be the day, which leaves 01 for the month. If you cannot rule out all other options, then you would have to return null.

Upvotes: 0

Aleksej
Aleksej

Reputation: 22949

Said that this is really a bad and dangerous way to store dates, you could try with something like the following:

select myColumn,
       coalesce(
                to_date(myColumn default null on conversion error, 'dd/mm/yyyy' ),
                to_date(myColumn default null on conversion error, 'yyyy mm dd' ),
                ...                                                 /* all the known formats */
                to_date(myColumn default null on conversion error ) /* dangerous */
                )
from myTable

Here I would list all the possiblly expected formats, in the right order and trying to avoid the to_date without format mask, which can be really dangerous and give you unexpected results.

Upvotes: 1

Related Questions