convert string to date functions

[bookouri]

I have a field that started out life as a free form text field and was then appropriated for date data. Unfortunately when they started putting date info in the field nobody set any formatting so I have a varchar2 field with dates in any format imaginable, 01012001, 1/1/91, 1-1-98, 12/02/2003, 1121998. Can anyone give me any pointers about how to convert all these? I already did the easy ones like replacing - with /. I can handle the ones that are consistently 01012002, but the other's are giving me a hard time...

thanks for any help...

 

[jimbopalmer]

eww the longest running bug in my code ever was a 'date' which used a zero instead of a capital O in 0ct, I finally found it years later printing the code on a dot matrix printer which used slashes in the zeros. best of luck

I tried to remain child-like, all I acheived was childish.

 

[SantaMufasa]

I believe I can create a solution to your problem if you can give me a listing of all possible date formats that occur in your hodge-podge character field. For instance, in your example, you mentioned "1121998"; is that "02Nov1998", or is it "12Jan1998"? And are all dates in some form of Month/Day/Year format, or are there occurrences of European Day/Month/Year formats? Also, how many rows are involved?

Once I get responses, I believe I can build you an automated solution.

Dave Hunt
801-733-5333

 

[BJCooperIT]

This might get you started:

http://oracle.ittoolbox.com/code/d.asp?d=2218&a=s

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant/Custom Forms & PL/SQL - Oracle 8.1.7 - Windows 2000

 

[bookouri]

thanks, the field might have any text.. at some point someone decided it would be a good place to put customer names.. go figure... but the date formats used are generally
01011999, 01/01/1999, 01-01-1999, 1/1/1999, 112999, and the two year variations, 010199, 01/01/99, 01/01/00, 1/1/00, 12399, and SOMETIMES they might have put in two dates, 01011999 01211999, 01/01/99 12/31/99, and SOMETIMES, I have found that the dates they put in are not valid dates...i.e.02/29/2006 might be a year when feb has 28 days.. but they didnt let that stop them..<G> the one thing nobody seems to have ever thought of was to use Feb 1, 1999, or 01-JAN-2000...

thanks for any help

 

[SantaMufasa]

Bookouri, I can handle each of your examples above except for &quot;12399&quot;; how does one determine whether the date is &quot;23Jan1999&quot; or &quot;03Dec1999&quot;? Or even worse, given &quot;112000&quot;, is it &quot;01Jan2000&quot; or &quot;20Nov2000&quot;?

To resolve your need quickest, I recommend e-mailing me at dave@dasages.com or call me at 801-733-5333. I'll be available today for about another hour (until 1400 Mountain time).

 

[SantaMufasa]

Bookouri, I built an Oracle function for you that handles all the character-string-to-date scenarios you listed in your thread (along with disclosing any ambiguous or erroneous date strings [per your specifications]). If you still need a solution, contact me via the e-mail or phone in my earlier response. (I thought better of pasting the code in this thread since the solution was 135 lines of code.)

Dave Hunt

 

[SantaMufasa]

Bookouri,

Do you want the character-string-to-date conversion function? It's waiting for you and getting lonely.

Dave Hunt
dave@dasages.com

 

[bookouri]

Sorry, I lost track of this thread. I actually fixed my data finally, by doing a lot by hand, but I got it done. I'd be glad to get a copy of your code though if you want to email it.

thanks

bookouri@hotmail.com