Using the to_date() function with inconsistent string formats

[LarrySteele]

We're building an intranet site where users are able to upload a spreadsheet that ultimately goes into an Oracle (10g) table. We want to store the date columns from the spreadsheet into date fields. Using the to_date function would be fine if the dates' format was consistent. However, we're seeing any of the following come through:
[tt]
m-d-yy
m-dd-yyyy
mm/d/yy
mm/dd/yyyy
[/tt]
Of course there are more variations on a theme, but you get the idea.

What I want to know is how to convert strings to dates when the date format is unknown and can vary.

I've tried Googling the answer, but haven't been able to conjure up the right phrase. I can't be the first to encounter this, so I'm sure there's more than one solution, I just haven't bumped into it yet.

Thanks in advance.

 

[SkipVought]

hi,

upload a spreadsheet

What method are you using to do this?

BTW, in Excel Date Values are NUMBERS. The Format is simply a display feature.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[LarrySteele]

Thanks Skip,

We're importing via a ColdFusion site. And yes, dates *can* be just numbers in Excel just as they can in Oracle. However they can also be just characters in both. There's a reason I'm a big proponent of storing dates in date fields.

 

[SkipVought]

However they can also be just characters in both

Then they are NOT dates and must FIRST be converted before doing anything of any consequence.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[LarrySteele]

Skip,

Agreed, that's what I was trying to do - convert date strings of various formats into dates.

Fortunately we found the solution. ColdFusion's dateFormat() function can take date strings of varying formats and turn them into date strings of specific format. Once the date string looks like 'mm/dd/yyyy', we're able to wrap with a to_date() function and store as dates in Oracle.

 

[SkipVought]

In Excel multiply 1 times any date range using Edit > Paste Special -- MULTIPLY and it will coerce any 'date' STRING to a real date value.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[carp]

Larry -
Santa Mufasa probably already has one in his archives, but it seems to me you could create a stored function that takes in a character string and returns a date. Something along the lines of

CREATE OR REPLACE FUNCTION varidate(p_string IN VARCHAR2) RETURN DATE AS
   l_date DATE;
   l_string VARCHAR2(50) := UPPER(p_string); -- CONVERTING TO UPPER DECREASES PERMUTATIONS OF FORMAT
BEGIN
   BEGIN
      l_date := TO_DATE(l_string, 'DD-MON-YYYY');
   EXCEPTION
      WHEN OTHERS THEN 
      BEGIN
         l_date := TO_DATE(l_string,'MM/DD/YYYY');
      EXCEPTION
         WHEN OTHERS THEN
         BEGIN
            l_date := TO_DATE(l_string,<next format>); 
         EXCEPTION
            .
            .
            .
            you get the idea
         END;
      END;          
   END;
   RETURN l_date;
END varidate;

Once in place, you can just feed your string into the function to get a date. For maximum efficiency, start with the format you see the most often and then work your way down. As each new format comes in, just add its handler in the next level of nesting.

I'll be the first to point out that this is an inelegant, brute force, blunt object approach. It can probably be cleaned up and simplified with regular expressions and a case statement. But at least it gets us back to your original question.

 

[SantaMufasa]

Santa Mufasa probably already has one in his archives...

I can't prove it, but as I read Larry's original post, I thought, "How would I solve this?" I thought of your way, Carp, and then saw that you had "pickpocketed" my brain, yet again. <grin>

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[carp]

SM -
Yep - I asked myself "What would Dave (of Sandy) do?" and there it was!

 

[SkipVought]

WWDoSD?

Make a wrist band!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[carp]

Great idea - we can start CodeStrong!

 

[LarrySteele]

Thanks carp. The way my searches were going, it seemed like an Oracle only solution would require a user defined function. I was hoping Oracle already had an overloaded version of their to_date() function that could interpret multiple formats.

We're using ColdFusion to read the spreadsheet data, and it has an overloaded date formatting function that converts date strings to dates.

As for the Oracle function, I agree that regular expressions would probably be more efficient and easier to write/maintain. Well, if you know how to work with regular expressions. After all this time, I still can't seem to wrap my head around them.