title: How does one handle exceptions in date casting in SAP's HANA?
body: <p><strong>QUESTION SUMMARY:</strong></p>
  
  <p>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?</p>
  
  <p>In SAP's table <a href="https://www.sapdatasheet.org/abap/tabl/konm.html" rel="nofollow noreferrer">KONM</a> the field <code>KSTBM</code> 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.)</p>
  
  <p>So valid values such as: </p>
  
  <ul>
  <li>201703290  (yep 2017 valid year 03 a valid month and 29 a valid day in that month for that year.)</li>
  <li>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)</li>
  </ul>
  
  <p>exist and can easily be cast using a <code>to_timestamp</code> or <code>daydate</code> or similar function.</p>
  
  <p>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:</p>
  
  <ul>
  <li>201702290  (Invalid day Feb didn't have 29 days in 2017)</li>
  <li>201713500  (Invalid Month 12 months not 13)</li>
  <li>201712312.400 (Invalid hour 00:00:00-23:59:59 there is no 24)</li>
  <li>201712310.060 (Invalid Minute 00:60 would be 01:00)</li>
  <li>201712310.090 (Invalid Minute 00:90 would be 01:30)</li>
  </ul>
  
  <p>When using <code>to_Timestamp</code> or <code>daydate</code> functions on such invalid records the following error is presented: </p>
  
  <blockquote>
    <p>SAP DBTech JDBC: [2048]: column store error: search table error:  [6860] invalid date, time or timestamp value;</p>
  </blockquote>
  
  <p>or</p>
  
  <blockquote>
    <p>[303]: invalid DATE, TIME or TIMESTAMP value:</p>
  </blockquote>
  
  <p>I agree those are invalid dates... and thus I know why the error is thrown.  </p>
  
  <p>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.</p>
  
  <p>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. </p>
  
  <p>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) </p>
  
  <p>Here's an example that could be run in HANA Studio:</p>
  
  <pre><code>  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
  </code></pre>
  
  <p>Change isBad to 0 and it works; change isBad to 1 and you see the error.</p>
  
  <p>Question Aspects: </p>
  
  <ol>
  <li>How can I get this query to run without error regardless of isBad being 1 or 0?</li>
  <li>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?</li>
  <li>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?</li>
  <li>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 <code>isdate()</code> 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)</li>
  </ol>
  
  <p>Thanks for reading my long drawn out question.  Hopefully I've provided sufficient details.</p>
  
  <p>I'm trying to avoid:</p>
  
  <ul>
  <li><a href="https://blogs.sap.com/2017/05/10/to_date-to-isdate-udf-to-check-whether-string-is-date-or-not/" rel="nofollow noreferrer">https://blogs.sap.com/2017/05/10/to_date-to-isdate-udf-to-check-whether-string-is-date-or-not/</a></li>
  <li>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...</li>
  </ul>