DB2 version is 9.7.0.7
I have a flat file, and need to validate fully prior to insert into a production table. For analysis, I've parsed it into a table where all columns are VARCHAR.
One of the tasks is to validate dates. I need to be able to locate the specific invalid dates, to report on the scope (frequency) and solution (reason).
I use ISDATE() in Sybase and SQL Server, which returns a 1 for a valid date, and a 0 for an invalid date. In Teradata, I left join to the SYS_CALENDAR table in the system catalog. It's been about 15 years since I've last been in a DB2 environment, but I believe analogs to either do not exist.
In this DB2 environment my role is limited to QA, meaning I cannot create T-SQL procedures or UDFs.
This thread is clever and makes me think there may be some Common Table Expression logic that could be employed in a query: ISDATE equivalent of DB2
That one falls short as a solution, however, because it only considers format - the presence of an invalid (but properly formatted) date like '2016-04-31' or '2016-02-30' will raise an error and the query will return no rows.
I need to return all rows, identifying if each is valid or invalid (or just return the invalid rows for investigation, even) - so doing a CAST or CONVERT, or inserting into a formatted table in a test environment won't work.
Is there an analog to ISDATE(), SYS_CALENDAR, or another solution that gets to the same end product of a row-wise presentation of dates that can't be cast to DATE, prior to performing that conversion/insert?
You can do it with the PureXML extension as follows:
SELECT
XMLCAST(XMLQUERY('string($D) castable as xs:date' PASSING mycolumn as D ) AS INT)
FROM
mytable
which will return 1 or 0.