×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Using the to_date() function with inconsistent string formats

Using the to_date() function with inconsistent string formats

Using the to_date() function with inconsistent string formats

(OP)
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:

m-d-yy
m-dd-yyyy
mm/d/yy
mm/dd/yyyy

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.





RE: Using the to_date() function with inconsistent string formats

hi,

Quote:

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,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Using the to_date() function with inconsistent string formats

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

RE: Using the to_date() function with inconsistent string formats

Quote:

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,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Using the to_date() function with inconsistent string formats

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

RE: Using the to_date() function with inconsistent string formats

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,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Using the to_date() function with inconsistent string formats

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

CODE

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.

RE: Using the to_date() function with inconsistent string formats

Quote (Carp)

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>

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

RE: Using the to_date() function with inconsistent string formats

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

RE: Using the to_date() function with inconsistent string formats

WWDoSD?

Make a wrist band!

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Using the to_date() function with inconsistent string formats

Great idea - we can start CodeStrong!

RE: Using the to_date() function with inconsistent string formats

(OP)
lol

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close