Geek Answers Handbook

Check dates before conversion, otherwise. Equivalent to ISDATE ()

DB2 Version - 9.7.0.7

I have a flat file, and it needs to be fully verified before being inserted into the production table. For analysis, I analyzed it in a table where all the columns are VARCHAR.

One of the tasks is checking dates. I need to find specific invalid dates, report the scale (frequency) and solution (reason).

I use ISDATE()in Sybase and SQL Server, which returns 1 for a valid date and 0 for an invalid date. In Teradata, I left the connection to the table SYS_CALENDARin the system directory. About 15 years have passed since the last time I was in the DB2 environment, but I believe that there are no analogues either. In this DB2 environment, my role is limited to QA, that is, I cannot create T-SQL or UDF procedures.

This thread is smart and makes me think there might be some common table expression logic that could be used in the query: the equivalent of DB2 ISDATE

However, this drawback is a solution, because it takes into account the format - the presence of an invalid (but correctly formatted) date, for example, "2016-04-31" or "2016-02-30", will lead to an error and the query will not return rows.

I need to return all the rows, indicating whether they are valid or invalid (or illegal return line to studies, even) - so what to do CASTor CONVERTor inserting in a formatted table in the test environment will not work.

ISDATE(), SYS_CALENDAR , , DATE, /?

+4
1

PureXML :

SELECT
 XMLCAST(XMLQUERY('string($D) castable as xs:date' PASSING mycolumn as D ) AS INT)
FROM 
 mytable

1 0.

+1

More articles: