How to check if string field contains a date?

[gdrenfrew]

Can I check if a string field contains a date? Is there an IS_DATE function in Oracle?

Thanks

 

[SantaMufasa]

GD,

Oracle does not have an IS_DATE function, but we can certainly create a user-defined function for you that should take care of business. But first, you must limit the universe of possible date formats that one might encounter in the string field. How might the dates appear?:

"mm/dd/yy"
"dd/mm/yy"
"mm/dd/yyyy"
"dd/mm/yyyy"
"dd-Mon-yy"
"dd-Mon-yyyy"
"dd Month YYYY"
"Month dd, YYYY"
et cetera

And also, could the date be embedded amongst other data in the string?

My ultimate question is: "If dates are a significant business need for your application, then why doesn't the database design store the significant dates in DATE columns?"

(If your manufacturing process is creating out-of-round ball bearings, do we create a "Ball-Bearing Rounder" machine or do we fix the manufacturing process to make the ball bearings perfectly round in the first place?)


Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[gdrenfrew]

Hi Santa,

I agree with your ultimate question, however I'm trying to use the Oracle database provided by a supplier, and it is utter rubbish. Hence I'm trying to write an extract to get the data out and insert it into SQL Server, which I've designed with appropriate data types.

Sometimes the Oracle "date fields" (really varchars, but called "InvoiceDate" etc.) are blank, sometimes they are "0", sometimes they have dates "dd.mm.yy" sometimes "dd/mm/yyyy" etc etc.

(NB If I wasn't using my real name as a login I'd happily publish the name of our supplier and their utter cobblers backoffice financial system).

So...
If I can assume that the contents of the field might fit into any of the date formats you mention, do I just need a big case statement?

 

[SantaMufasa]

GD,

Question 1: "If I can assume that the contents of the field might fit into any of the date formats you mention, do I just need a big case statement?"

Answer 1: If I were in your predicament, then I would create a function to extract dates from the source column:

CREATE OR REPLACE FUNCTION scrub_date (rubbish_in varchar2) RETURN DATE IS
    scrubbed_date_out DATE;
...
(all the appropriate CASE statements to determine if the string is a DATE. If the code encounters no valid DATE, then return NULL; otherwise return a valid DATE in a proper Oracle DATE expression. Either way, you will ultimately say...)
    RETURN scrubbed_date_out;
END;
/

Question 2: "Who should have to pay (either in time or money) to produce a solution for this problem?"

Answer 2: If my company authored this (non-)application, I would be totally embarrassed to show my face. If this (correctly labeled "Rubbish") is from a supplier, then they should have to underwrite the costs of "un-rubbishing" this particular problem. This is tatamount to "IT Malpractice". If the supplier is unwilling to provide a quality, working fix (at the bare minimum, the code for the above scrub function), then I would get the corporate attorneys involved...not from a cost-effectiveness standpoint, but because of the principle involved: No one should be able to revenue on crap like theirs.

I would be happy to testify as an expert witness in your behalf.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[gdrenfrew]

Cheers Santa,
can't say much, but advice has already been taken from the Eagles on this supplier's product, and the issue with dates is a teeny tiny problem in a much grander pile of poo!

I'll tack in the date function you suggest.
thanks again.