4

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?

Community
  • 1
  • 1
geolemon
  • 43
  • 5
  • If you could determine a way to do this in a stored procedure, then the matter would be a "tools for the job" problem which you could take up with those paying you. – Dan Bracuk May 05 '16 at 16:44
  • 1
    You can run a compound SQL statement (sometimes called an anonymous block) without creating a routine. So, yes, there is a solution. – mustaccio May 05 '16 at 17:17

1 Answers1

1

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.

Stavr00
  • 3,219
  • 1
  • 16
  • 28
  • 1
    Neat! I should probably point out that pureXML is not an extension, but a part of the DB2 query processing engine. – mustaccio May 05 '16 at 17:46
  • Thanks, I'm going to have to read up on this - something new to learn! Definitely looks simple and efficient - hopefully no penalty on performance - and I'm not currently sure how involved the DBMS is - if this is just presentation layer, or if could still raise a DBMS error? I'll read up on it, thanks! – geolemon May 05 '16 at 17:50
  • This mostly works, but the result displays this long string, rather than just "false" or "true" - "false" The prefix is unnecessary, but I can't find documentation that explains the string, or how to suppress it. I could LOCATE and SUBSTR, but only if necessary. Thanks! – geolemon May 05 '16 at 18:35
  • 1
    The DB app I use does not show me the XML preamble. Try wrapping it inside `XMLCAST( ... AS CHAR)` – Stavr00 May 05 '16 at 19:09
  • Works perfectly if I wrap it in an XMLCAST( ... AS VARCHAR(10)). Solved - thanks!! – geolemon May 05 '16 at 19:53