How does one handle exceptions in date casting in SAP's HANA?

Question

QUESTION SUMMARY:

What is the proper way within HANA SQL or HANA Studio in a view (Calculation, attribute or Analytic) to handle invalid data when attempting to cast to a date so a user can filter the data?

In SAP's table KONM the field KSTBM is a datatype of decimal(15,3). This field right or wrong, stores a date value in YYYYMMDDHHM.MSS format. (I'm a user not a designer of the system. Why anyone stores a date in a decimal field instead of a oh a date/time field is beyond the purview of this question.)

So valid values such as:

201703290 (yep 2017 valid year 03 a valid month and 29 a valid day in that month for that year.)
201703301.130 (yep 2017 valid year 03 a valid month and 30 a valid day in that month for that year along with 11:30 being a valid time)

exist and can easily be cast using a to_timestamp or daydate or similar function.

Unfortunately a few bad entries have been made into this table resulting in data that is unable to be cast to a date time such as:

201702290 (Invalid day Feb didn't have 29 days in 2017)
201713500 (Invalid Month 12 months not 13)
201712312.400 (Invalid hour 00:00:00-23:59:59 there is no 24)
201712310.060 (Invalid Minute 00:60 would be 01:00)
201712310.090 (Invalid Minute 00:90 would be 01:30)

When using to_Timestamp or daydate functions on such invalid records the following error is presented:

SAP DBTech JDBC: [2048]: column store error: search table error: [6860] invalid date, time or timestamp value;

or

[303]: invalid DATE, TIME or TIMESTAMP value:

I agree those are invalid dates... and thus I know why the error is thrown.

While I would love to fix the root cause and then correct the bad data; that isn't presently an option. Different teams, different resources different priorities. So it's on their list of things to do but I have users who need the WEBI reports; and since the bad data exists... I still have to handle it.

What I'm trying to do is create a Calculated_view in HANA Studio which can handle these erroneous dates successfully. But I've not been able to find a try catch or other type of exception handling which would allow me to basically set these dates to NULL so the user still gets the other relevant data and are able to see they have some bad data in the system that the could correct.

As it stands since this error is thrown, no records are able to be returned from the universe when the WEBI report is run. I've found a few options that involve creating a date/time table with all possible times.... (I hope you can see why I don't want to do that) or creating a function (but it lacks specific directions; I'm afterall a new user to HANA and Universes and WEBI which is why the question exists)

Here's an example that could be run in 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

Change isBad to 0 and it works; change isBad to 1 and you see the error.

Question Aspects:

How can I get this query to run without error regardless of isBad being 1 or 0?
Is there a method/manner to include/not include the bad data (perhaps set all bad data to NULL in the result and null data can then be included/excluded as a user's option?
is there a way in a view to identify these bad records in a calculated column so we don't try and convert them if invalid and do try when valid?
Is my approach simply wrong and I need to retrain my Oracle/MS SQL/MySQL brain to think differently? Other languages I'd handle exceptions, or try catch or use isdate() to check for valid before attempting to cast... I just don't see those options here (but I'm new and perhaps simply unable to use the help very well yet)

Thanks for reading my long drawn out question. Hopefully I've provided sufficient details.

I'm trying to avoid:

https://blogs.sap.com/2017/05/10/to_date-to-isdate-udf-to-check-whether-string-is-date-or-not/
and DATE_CHECK_PLAUSIBILITY is an ABAP function (I'm looking for a SQL/HANA Studio one) but this is along the right path.... I think...

Upvote for a really well-prepared question including an executable example. Good job! — Lars Br., Apr 06 '18 at 02:45
Supplemental: While we can do this in the hana queries, I don't recommend it from a performance standpoint if these fields will be used as filters in queries. The casting to date performance is god awful on large datasets; better off to have your users search on the string or decimal dates. (sigh) I believe this has to do with lack of index on a function based result. — xQbert, Jun 30 '20 at 14:26
You can create a kind of "function-based-index" by creating a computed column. However, even the performance guide mentions that it's usually best to convert the compared literal value into the correct data type/format instead of the compared column. — Lars Br., Jun 30 '20 at 23:09
Maybe were doing something wrong then. If you're saying to cast the string date to a date in the Calculation view; and allow the user to pass in a "date" datatype to filter... We did this using "Calculated Columns" in Hana. We then set a filter on this calculated column from BO universe linked to the calculation view; performance was horrid. When we used the string date in the universe instead of the calculated column performance improved 10 fold. maybe this i because the result sets returned often contain 100,000+ records for a given top level product hierarchy. — xQbert, Jul 01 '20 at 12:52

score 3 · Accepted Answer · edited Jul 08 '19 at 17:54

You might want to use the tstmp_is_valid() function for this:

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

See the CDS documentation on that.

There's also a function: DATS_IS_VALID("STRINGDATE") which will evaluate a date and return a 1 or 0. 1 being if date is a valid date.

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;

In the above example we simply convert a date to a valid date and set it to null when not valid in the results. Useful if you have string dates that are getting saved but not valid.

Perfect! I was looking for a function that would be something like isDate() in SQL server! That documentation may be the source I'm looking for as well. Thank you! am I to take it that ABAP functions can be called from within Hana studio (it works so I guess so)? Do you know if that approach does context switches which would slow performance of the query like Oracle does when switching from SQL to PL/SQL? — xQbert, Apr 06 '18 at 12:33
By context switches I mean switch from one Engine to another which is known to slow performance. Granted, it works and my philosophy is do something, laugh, do it right, then make it better. I'm on the do something and laughing phase right now. but asking about the do it right and make it better one in preparation for growth. — xQbert, Apr 06 '18 at 13:06
That's a lot of questions... quick answers: no, you cannot take any ABAP function and run it in HANA (the one I mentioned is a HANA specific implementation of a DB independent CDS function). The function is handled like any other built-in SQL function and no extra "context"-switch is required - it's not a SQLScript function, but built-in. Hope that helps. — Lars Br., Apr 07 '18 at 10:44

score 0 · Answer 2 · answered Feb 24 '23 at 09:24

0

Your solution seemed to provide an answer to an issue we were facing. However, tstmp_is_valid() does not to seem to provide the correct answer in some situations. For example: tstmp_is_valid(10101000000) returns 1, but since the date is incorrect one would expect a 0.

answered Feb 24 '23 at 09:24

Antonie Tak

1

This should be a new question since the answer was accepted. I will note your numeric value lacks 3 positions m:ss, it has yyyymmddhhm for a timestamp. If I add those missing values as 000 and run your compare it comes up invalid. `SELECT tstmp_is_valid(10101000000000) from dummy;` – xQbert Feb 24 '23 at 14:11
Is it possible you have the field being stored in an int data type when it should be decimial with 3 digits after the decimal which is causing those 3 to fall off? – xQbert Feb 24 '23 at 14:13

How does one handle exceptions in date casting in SAP's HANA?

2 Answers2

Linked