Determine if valid date via SQL

[patrickbachmann]

Hi folks,

I'm trying to find the equivalent of IS_DATE function to determine if a field contains a valid date in SQL select statement;

ie: something like this

select

CASE WHEN IS_DATE(TO_CHAR(BLDAT, 'MM/DD/YYYY')) = 'Y'

THEN

<DO SOMETHING>

ELSE

<DO SOMETHING ELSE>

END As DateCheck

etc.....

Except IS_DATE is not a valid function in HANA.  Anybody know another way to do this?

Thanks,

-Patrick

[henrique_pinto · Accepted solution]

Hi Patrick,

a different approach would be to make use of the embedded "Generate Time Data" functionality in HANA, and then check whether your date exists in the output table.

In HANA Quick Launch view, go to Data -> Generate Time Data and then fill with your desired parameters. Basically the difference between 'Gregorian' and 'Fiscal' is that 'Gregorian' will fill the "_SYS_BI"."M_TIME_DIMENSION" table, while 'Fiscal' will fill the "_SYS_BI"."M_FISCAL_CALENDAR" table. Chose the range you want, but notice that for 'Day' granularity (which is the granularity I suppose you need), it will allow you to do it for max of 50 years - meaning, you'll need to break your desired year range in 50-year batches, i.e. if you want to generate it for 1901-2100, you'll need to run it 4 times, 1901-1950, 1951-2000, 2001-2050 and 2051-2100.

In the end, you could run a SELECT against this table with a WHERE clause on your desired input date against the DATE_SAP column. For example:

SELECT CASE WHEN A = 0 THEN 'FALSE' ELSE 'TRUE' END IS_DATE FROM (
          SELECT COUNT(*) A FROM "_SYS_BI"."M_TIME_DIMENSION" WHERE DATE_SAP = '20130228'
)
IS_DATE
TRUE   
SELECT CASE WHEN A = 0 THEN 'FALSE' ELSE 'TRUE' END IS_DATE FROM (
          SELECT COUNT(*) A FROM "_SYS_BI"."M_TIME_DIMENSION" WHERE DATE_SAP = '20130229'
)
IS_DATE
FALSE 

Hope that helps.

Best regards,

Henrique.

[patrickbachmann · Accepted solution]

Ravi, yes exactly what I was thinking too, that I would have to account for the varying number of days in different months which could be cumbersome.

Henrique, I do have time data generated and this is an interesting alternative. One I think that would be slower but I would like to test it.  I get your concept however I'm stuck on the 'CASE WHEN A=0' portion.  I'm not sure what you intended me to do there.

-Patrick

[henrique_pinto · Accepted solution]

'A' is just an alias for the COUNT of the inner select (I didn't want to have to write COUNT(*) two times again). Basically, the inner select checks whether the desired date exists in the M_TIME_DIMENSION table. If it doesn't, COUNT = 0 (i.e. A = 0) and then IS_DATE (an alias for the CASE statement) is false. If it exists, then COUNT = 1, and IS_DATE is true.

It's probably slower however that select runs really fast (6ms in my case) so I'm not sure it'll have any considerable impact in your case. Unless of course you do that in a loop, in which case it'd be more interesting to do a join of your input table and the M_TIME_DIMENSION table and see which records exist in both.

Best regards,

Henrique.

[patrickbachmann · Accepted solution]

Haven't forgotten about this, still tinkering and will update soon.... -Patrick

[former_member184768 · Accepted solution]

Hi Patrick,

My 2 cents:

1) If the source system is providing the junk date values, then let the ETL tool handle rejecting the date values. Depending on your ETL, you can reject the incorrect records.

2) If the requirement is to check the user input value, then it should be handled by the front end reporting tool (like MSTR / BO).

3) Alternatively, the option suggested by Henrique is really good and can be implemented. But I think such a check at each value level may have performance impact.

Regards,

Ravi

[patrickbachmann · Accepted solution]

Guys I finally got around to testing Henrique's suggestion and got it to work pretty nicely.  I haven't done any performance analysis on it yet but for now we have fixed our bad dates on the source system side.  Thanks for your help!

-Patrick

[justin_molenaur2 · Accepted solution]

Was hitting a couple snags with bad data delivered from SLT (source of ECC) during creation of views and thought I would weigh in.

In my case, I was tossing out anything that had a bad date, so I leveraged a time dimension (not M_TIME_DIMENSION but similar) as an INNER join on the date column. In this way, any dates that were in the bad format get dropped because of the join.

This was a helpful thread during investigation though.

Regards,

Justin

[jfuentes13]

Thanks, Henrique Pinto. The table "_SYS_BI"."M_TIME_DIMENSION" is very useful.

[former_member206376]

This message was moderated.

[former_member184768]

Hi Patrick,

I would be really interested in following this thread. This is what I could do so far:

CREATE FUNCTION FN_IS_DATE (v_in_date nvarchar(10))

RETURNS table (a TINYINT) LANGUAGE SQLSCRIPT AS

   v_out tinyint;

   v_p date;

BEGIN

DECLARE EXIT HANDLER FOR SQL_ERROR_CODE 303 SELECT ::SQL_ERROR_CODE, ::SQL_ERROR_MESSAGE FROM DUMMY;

begin

select to_date(:v_in_date, 'MM/DD/YYYY') into v_p from dummy;

end;

if :v_p is not null then

   v_out := 1;

else

   v_out := 0;

end if;

return select :v_out as a from dummy;

END;

select a from FN_IS_DATE('02/02/2012');

select a from FN_IS_DATE('02/30/2012');

I tried to use the above function in another procedure to see how it works:

create procedure PR_IS_DATE1(out y varchar(10))

LANGUAGE SQLSCRIPT

as

v_pp tinyint := 9;

BEGIN

select a into v_pp from FN_IS_DATE('02/30/2012');

if :v_pp = 9 then

   select 'OO' into y from dummy;

else

   select 'NN' into y from dummy;

end if;

END;

call PR_IS_DATE1(?);

But it doesn't really work. I'll try some more attempts. Let's see if somebody else can come up with another better concept.

Regards,

Ravi

[patrickbachmann]

Ok I tried Henrique's idea and unfortunately I just got error 'attribute value is not a date';

select TO_DATE(BLDAT) from "MYSCHEMA"."MKPF" WHERE BLDAT = '03022009' <--- notice bad date

Somehow I think I need a case statement built around this but I have not figured syntax out yet.  Just digesting your comments now Ravi.

[henrique_pinto]

Hi Ravi,

exception handling in SQL is kinda tricky.

Maybe, what could be done instead is to handle it in Server Side JavaScript, i.e. within a .xsjs file of XS Engine.

For instance, the HANA Developer Guide mentions the following example:

var conn;
try {
     conn = $.db.getConnection();
     var query = 'SELECT SYSTEM_ID, HOST, SID, INSTANCE, PORT, USER, PASSWORD,      COMMENT FROM ' + 'ACME_XSTC.com.acme.adm.xstc.db::SYSTEM WHERE SYSTEM_ID = ?';
     var pstmt = conn.prepareStatement(query);
     pstmt.setInteger(1, id);
     var rs = pstmt.executeQuery();
     if (rs.next()) {
          return {
               id : rs.getInteger(1),
               host : rs.getNString(2),
               sid : rs.getString(3),
               instance : rs.getDecimal(4),
               port : rs.getDecimal(5),
               user : rs.getNString(6),
               password : rs.getNString(7),
               comment : rs.getNString(8)
          };
     }
} catch (ex) {
     $.trace.error("Unable to retrieve system. " + ex.toString());
     ex.source = fileName;
     throw ex;
} finally {
     if (conn) {
          conn.close();
     }
}

@Patrick,

for you, this would mean that you'd need to call a .xsjs service in order to run this instead of running it from the SQL Editor. I'm not sure that meets your requirements...

Best regards,

Henrique.

[former_member184768]

, I already knew that my procedure may not work as current implementation of Exception handler in SQL does not provide TRY CATCH type functionality.

To my knowledge, Patrick has MSTR as the front end tool. The XS option may not be available and I am not sure if the date handling is possible in MSTR.

Regards,

Ravi

[patrickbachmann]

Ravi, you are exactly right we are consuming this view via Microstrategy and dates are being selected with this TO_CHAR function.  We do consume HANA in other ways but right now this is our primary front end so I'm not sure the XS would work in this case.  I will read more about it today though.

-Patrick

[patrickbachmann]

Ok this is a bit ugly but in this experiment I tried, if it finds a bad DAY greater than 31 or a MONTH greater than 12 it substitutes with a fake date 01/01/9999.   Of course it doesn't take into account a date such as February 31st which would never exist though.

select

CASE WHEN RIGHT(LEFT(BLDAT,6),2) > '12'

THEN

'01/01/9999'

WHEN RIGHT(BLDAT,2) > '31'

THEN

'01/01/9999'

ELSE

TO_CHAR(BLDAT,'MM/DD/YYYY')

END As Doc_Date

from "MYSCHEMA"."MKPF"

[former_member184768]

Hi Patrick,

Do you also need to check if there is date 31 in months like April, June, Sept etc.. .

Or we are just making it complex ?

Regards,

Ravi

[henrique_pinto]

Maybe you could try to use TO_DATE() and if it successfully converts, then you can consider it was a valid date?

I'm not sure how to capture the result, though.

You need to test to see what is the output of TO_DATE() in case you have an invalid date, or if it will run at all or just throw an error (in which case I suppose it's unusable?).

http://help.sap.com/hana/html/sql_function_to_date.html