DB2 Field Data Manipulation

[digitalpicasso]

Hi Everyone!

This is my first post - and if it troubles you, my apologies.

I am using DB2 UDB v7.1 on Windows NT.

I have a table "application" and it has a char field called "dateinstock" within it.

This Character field is used to store Dates within it - in the following format: MM/DD/YYYY

However - during mass data upload - users have inputted data in all sorts of stupid formats -

09091999, 09/09/19995 etc. etc. etc.

There are also instances where there are "blanks" encountered.

Humans can interpret these errors, however, when I run this query:

select year(dateinstock) from application

there is a date/time value mismatch and understandbly so.

So I ran this particular script:

CASE (SUBSTR(dateinstock,3,1) )
WHEN '/' THEN dateinstock
ELSE
INSERT
(INSERT(dateinstock, 3, 0, '/'), 6,0,'/' )
END

This takes a date like 09091998 and makes it - 09/09/1998

However, does any have a script that has exceptions for all other anomalies in the date format? Like if the date is too large - can u trim it? If the date is just a "space" can you NULL it? Unless those conditions are accounted for - the data will remain dirty and therefore a query like:

select year(dateinstock) from application won't function properly.

Any solutions? Does any have an application or a DB2 Script that does the cleaning out for you?

Sincerely,

Zubin R. Wadia