在转换之前验证日期，也就是.ISDATE() 等效 [英] Validate dates before conversion, aka. ISDATE() equivalent

问题描述

DB2 版本为 9.7.0.7

DB2 version is 9.7.0.7

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

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).

我在 Sybase 和 SQL Server 中使用 ISDATE()，它返回 1 表示有效日期，返回 0 表示无效日期.在 Teradata 中，我将连接留在系统目录中的 SYS_CALENDAR 表中.自从我上次进入 DB2 环境已经过去了大约 15 年，但我相信这两者的类似物都不存在.在这个 DB2 环境中，我的角色仅限于 QA，这意味着我不能创建 T-SQL 过程或 UDF.

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.

这个线程很聪明，让我觉得可能有一些通用表表达式逻辑可以在查询中使用:ISDATE 等效于 DB2

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

然而，这作为一种解决方案还不够，因为它只考虑格式 - 存在无效(但格式正确)的日期，如2016-04-31"或2016-02-30"会引发错误并且查询将不返回任何行.

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.

我需要返回所有行，确定每个行是有效还是无效(或者只返回无效行进行调查，甚至) - 所以执行 CAST 或 CONVERT, 或在测试环境中插入格式化表格将不起作用.

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.

是否有类似于 ISDATE()、SYS_CALENDAR 或其他解决方案的解决方案，可以得到相同的最终结果，即不能按行显示日期在执行转换/插入之前被强制转换为 DATE?

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?

推荐答案

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

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

这将返回 1 或 0.

which will return 1 or 0.

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