验证转换前的日期，也称为。 ISDATE（）等效 [英] Validate dates before conversion, aka. ISDATE() equivalent

问题描述

DB2版本是9.7.0.7

我有一个平面文件，需要在插入到生产表之前完全验证。为了分析，我已将其解析成一个表，其中所有列都是 VARCHAR 。

其中一个任务是验证日期。我需要找到具体的无效日期，报告范围（频率）和解决方案（原因）。

我使用 ISDATE （）在Sybase和SQL Server中，它为有效日期返回1，对于无效日期返回0。在Teradata中，我将加入到系统目录中的 SYS_CALENDAR 表。自从我上次进入DB2环境以来已经有15年了，但是我相信模拟不存在。
在这个DB2环境中，我的角色仅限于QA，这意味着我无法创建T-SQL过程或UDF。

这个线程很聪明，让我觉得可能是一些可以在查询中使用的通用表表达式逻辑：
ISDATE相当于DB2

然而，由于它只考虑格式 - 因为存在无效（但格式正确）的日期，如'2016-04-31'或'2016-02-30'将引发错误，查询将不会返回任何行。

我需要返回所有行，标识每个是否有效或无效（或者只是返回无效的行进行调查，甚至） - 所以做一个 CAST 或 CONVERT ，或在测试环境中插入格式化的表格将无法正常工作。

有没有模拟到 ISDATE（）， SYS_CALENDAR ，或另一个获得相同的解决方案在执行该转换/插入之前，不能将其转换为 DATE 的日期的逐行显示的最终产品

解决方案

您可以使用 PureXML 扩展名，如下所示：

pre $ SELECT
XMLCAST（XMLQUERY（'string（$ D）castable as xs：date'PASSING mycolumn as D）AS INT）
FROM
mytable


这将返回1或0。

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.

这篇关于验证转换前的日期，也称为。 ISDATE（）等效的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！