如何处理SAP的HANA中的日期转换异常？

Question

问题摘要：

在视图(计算、属性或分析)中的 HANA SQL 或 HANA Studio 中，在尝试强制转换为日期以便用户可以筛选数据时，处理无效数据的正确方法是什么？

在 SAP 的表 KONM 中，字段KSTBM是十进制 (15,3) 的数据类型。 此字段正确或错误，将日期值存储在 YYYYMMDDHHM 中。MSS 格式。(我是用户而不是系统的设计者。为什么有人将日期存储在十进制字段中而不是哦日期/时间字段中超出了这个问题的范围。

因此，有效值如下：

• 201703290(是的，2017 年有效年份 03 是有效月份，29 是该年该月的有效日期。
• 201703301.130(是的，2017 年有效年份 03 是该年的有效月份，30 是该年的有效日期，11：30 是有效时间)

存在，并且可以使用to_timestamp或daydate或类似函数轻松转换。

遗憾的是，在此表中输入了一些错误的条目，导致数据无法转换为日期时间，例如：

• 201702290(2017 年 2 月无效日期没有 29 天)
• 201713500(无效月份为12个月，而不是13个月)
• 201712312.400(无效小时 00：00：00-23：59：59 没有 24)
• 201712310.060(无效分钟 00：60 为 01：00)
• 201712310.090(无效分钟00：90为01：30)

在此类无效记录上使用to_Timestamp或daydate函数时，会出现以下错误：

SAP 数据库 JDBC：[2048]：列存储错误：搜索表错误：[6860] 日期、时间或时间戳值无效;

或

[303]：无效的日期、时间或时间戳值：

我同意这些是无效的日期...因此我知道为什么会抛出错误。

虽然我很想解决根本原因，然后纠正错误数据; 这目前不是一种选择。 不同的团队，不同的资源，不同的优先级。 所以这是他们要做的事情清单，但我有需要 WEBI 报告的用户;而且由于存在不良数据...我仍然必须处理它。

我要做的是在 HANA Studio 中创建一个可以成功处理这些错误日期的Calculated_view。 但是我无法找到尝试捕获或其他类型的异常处理，这将允许我基本上将这些日期设置为 NULL，以便用户仍然获得其他相关数据，并能够看到他们在系统中有一些可以纠正的错误数据。

就目前而言，由于抛出此错误，因此在运行WEBI报告时，无法从宇宙中返回任何记录。 我发现了一些选项，涉及创建包含所有可能时间的日期/时间表......(我希望你能明白为什么我不想这样做)或创建一个函数(但它缺乏特定的方向;毕竟我是HANA和宇宙以及WEBI的新用户，这就是为什么存在这个问题的原因)

下面是可在 HANA Studio 中运行的示例：

WITH MyExample as (SELECT 201701011.230 as KSTBM, 0 isBad from dummy union all
SELECT 201702301.000 as KSTBM, 1 isBad from dummy union all
SELECT 201702171.230 as KSTBM, 0 isBad from dummy union all
SELECT 201702192.400 as KSTBM, 1 isBad from dummy)
SELECT to_timestamp(To_DECIMAL(KSTBM*100000,15,0)) TS, 
isBad
FROM MyExample A
WHERE isBad = 1

将 isBad 更改为 0 即可工作;将 isBad 更改为 1，您会看到错误。

问题方面：

1. 如何让此查询运行而不会出错，无论 isBad 是 1 还是 0？
2. 是否有一种方法/方式可以包含/不包含错误数据(也许在结果中将所有错误数据设置为 NULL，然后可以将空数据作为用户选项包含/排除？
3. 有没有办法在计算列中识别这些不良记录，以便我们不会在无效时尝试转换它们，而在有效时尝试转换它们？
4. 我的方法是否完全错误，我需要重新训练我的Oracle/MS SQL/MySQL大脑以不同的方式思考？ 其他语言我会处理异常，或者在尝试投射之前尝试捕获或使用isdate()来检查是否有效......我只是在这里看不到这些选项(但我是新手，也许根本无法很好地使用帮助)

感谢您阅读我冗长的问题。 希望我已经提供了足够的细节。

我试图避免：

• https://blogs.sap.com/2017/05/10/to_date-to-isdate-udf-to-check-whether-string-is-date-or-not/
• DATE_CHECK_PLAUSIBILITY是一个ABAP函数(我正在寻找SQL/HANA Studio函数)，但这是正确的道路。我认为。。。

Answer 1

您可能希望为此使用tstmp_is_valid()函数：

WITH MyExample as (SELECT 201701011.230 as KSTBM, 0 isBad from dummy union all
SELECT 201702301.000 as KSTBM, 1 isBad from dummy union all
SELECT 201702171.230 as KSTBM, 0 isBad from dummy union all
SELECT 201702192.400 as KSTBM, 1 isBad from dummy)
SELECT KSTBM,
tstmp_is_valid(KSTBM*100000), 
isBad
FROM MyExample A;
KSTBM           TSTMP_IS_VALID(KSTBM*100000)    ISBAD
201,701,011.23  1                               0    
201,702,301     0                               1    
201,702,171.23  1                               0    
201,702,192.4   0                               1    

请参阅CDS文档。

还有一个函数：DATS_IS_VALID("STRINGDATE")它将计算一个日期并返回 1 或 0。 1 表示日期是否为有效日期。

WITH CTE AS 
(SELECT '00000000' as STRINGDATE from dummy union all
SELECT '20190101'  from dummy union all
SELECT '20190230' from dummy union all
SELECT '20191301' from dummy union all
SELECT '20191232' from dummy union all
SELECT '20190228' from dummy union all
SELECT '20200228' from dummy union all
SELECT '20200229' from dummy )
SELECT StringDate, DATS_IS_VALID("STRINGDATE") isValid, case when DATS_IS_VALID("STRINGDATE") =1 then 
cast("STRINGDATE" as date) else cast(null as date) end RightDataType from CTE;

在上面的示例中，我们只是将日期转换为有效日期，并在结果中无效时将其设置为 null。 如果您有正在保存但无效的字符串日期，则很有用。

Answer 2

您的解决方案似乎为我们面临的问题提供了答案。但是，在某些情况下，tstmp_is_valid() 似乎不能提供正确的答案。 例如：tstmp_is_valid(10101000000) 返回 1，但由于日期不正确，因此预期为 0。