How to check constructed string is timestamp format or not in SQL Script?

[former_member191072]

Hi Gurus,

I want to check string is correct timestamp format or not in SQLScript procedure .

like followings

------------------

dt = '2015/08/99'

ti = '00:00:00'

dtstr = dt || ' ' || ti

------------------

I want to check dtstr is correct datetime or not before next process.

*above example is 'wrong' , '2015/08/99' is not correct date.

But it seems to me , HANA doesn't have ISTIMESTAMP() / ISDATE()  / ISTIME() or similar functions.

Do you have any idea ?

Regards,

JT

[NaotoSakai · Accepted solution]

Hi JT

How about this ? You can customize following function to your required.

------------------

CREATE FUNCTION isDateTime(inputval NVARCHAR(30))

RETURNS RETURNVAL INTEGER

LANGUAGE SQLSCRIPT

AS

BEGIN

DECLARE DT TimeStamp;

   DECLARE EXIT HANDLER FOR SQLEXCEPTION

        BEGIN

   RETURNVAL := 0;

        END;

    select To_TimeStamp(:inputVal) INTO DT from dummy; //If inputval is strange format , this statement will raise error then go to handler , returnval=0.

    RETURNVAL := 1;

END;

-----------------

Do at your own risk !

Regards,

Naoto

[former_member191072 · Accepted solution]

Thank you Naoto !

I'll modify your UDF to ISDATE() and ISTIME() .

Thanks again,

JT

[Former Member]

Hi Jim

One option you could use is to Generate Time Data and compare the data generated to your date to see if it exists there and is therefore valid.  More details on generating time data are in the SAP HANA Developer Guide for SAP HANA Studio downloadable from service.sap.com/hana

Ruth