Transforming an ISO 8601 timestamp in HANA

[former_member314792]

While working on a flow to transform a date in the ISO 8601 format into a HANA TIMESTAMP, I'm getting unexpected results.  A typical input row contains '2016-01-19T06:07:08.473Z' as EncounterDate (there are also instances that do not contain fractional seconds).

First of all, the timestamp format 'YYYY-MM-DDTHH24:MI:SS.FF3' isn't accepted by to_timestamp() function, with the 'T' separator not being recognized, nor is the zone adjustment (e.g. 'Z') following the seconds and fraction.   So, after trimming of the last character of the NVARCHAR string, and doing a replace() of the 'T' with a space, this error occurs:

SQL:  select to_timestamp(replace(left("EncounterDate", length("EncounterDate")-1),'T', ' ')) as "Encounter_DTM"  from xxx;

Error:  Could not execute 'select to_timestamp(replace(left("EncounterDate", length("EncounterDate")-1),'T', ' ')) as ...' in 218 ms 354 µs .

[303]: invalid DATE, TIME or TIMESTAMP value: search table error:  [6931] attribute value is not a date or wrong syntax;longdate [here]longdate(string fox_replace(string midstru(string "EncounterDate", int '1', int sqladd(int chars(string "EncounterDate"), int '-1')), string 'T', string ' ')),EncounterDate = '68788986903'[string]; checkNull false

However, changing to a substr_before() corrects the remove-last-character issue, but not the 'T' one, so a replace() is still needed:

SQL:  select to_timestamp(replace(substr_before("EncounterDate",'Z'), 'T',' ')) as "Encounter_DTM"  from xxx;

This seems like a length() issue, but the input column does not contain multi-byte characters.  There also seem to be some intermediate conversions going on, as that value "68788986903" for EncounterDate noted in the error does not appear in the input column.

HANA is SPS09, Studio at Patch 97.

Has this error been encountered before?  Is there an ISO 8601 date format function in the works?

Much Thanks,

  Donn

[lucas_oliveira · Accepted solution]

Hi Donn,

Maybe your transformation is taking place somewhere outside HANA and that tool/db is not being able to parse the value?

I ask that because I've tried a 'select to_timestamp('2016-01-19T06:07:08.473Z') from dummy' on 3 different HANA revisions (97.03, 102.03 and 110) with no issues at all.  So as far as I could see you can use that kind of format and to_timestamp will parse it.

Another point: sometimes we get fooled by the front-end cell formatting features. Confirm if your source table is within HANA and the format is exactly as you mentioned. So far I can't see why a to_timestamp would not work.

BRs,

Lucas de Oliveira

[former_member314792 · Accepted solution]

Lucas,

That's good to know.  I'm running the transformation on an nvarchar field that was loaded by BODS import flow.  No other processing is being done, but I did find three odd values that don't conform at all to the pattern.  Now I have to move this over to BODS, where there is no to_timestamp() function, but perhaps the to_date() will work OK.

Still having a problem with null characters, but I'll ask it in a different posting.

Thanks,

  Donn

[lucas_oliveira · Accepted solution]

Wait, so the transformation is taking place on the ETL side and not on HANA?

If that's the case, then you'll need to make sure you obey to the underlying DB rules (Oracle, MS SQL Server, etc).

Anyhow, most of the relational databases out there (if not all) will offer IFNULL function.

Regards,

Lucas de Oliveira

[former_member314792 · Accepted solution]

Lucas,

I've tested on both the ETL (BODS) and HANA side; the HANA version does not recognize the additional "T" and "Z" characters in the format pattern, while in BODS those are accepted and produce the desired results.  It would be nice if there was a notation ("[..]"?) to indicate an optional part of the format when converting input, but I've worked around that.

I have another thread tracking the NULL issue (in the BODS section), which has a couple of interesting wrinkles since the functions there treat nulls differently than other characters.

Thanks,

  Donn

[former_member314792 · Accepted solution]

Lucas,  p.s.:

The to_timestamp() on HANA does convert an ISO 8601 timestamp properly only if the format string is not provided; however it does not accept the "T" and "Z" as part of an otherwise valid format (e.g. to_timestamp('2016-01-19T23:45:22.678Z', 'YYYY-MM-DDTHH24:MI:SS.FF3Z') ). 

This action seems counterintuitive to me, leading to the results I am seeing.

Donn

[lucas_oliveira · Accepted solution]

Hi Don,

Yes, as I mentioned on my first reply trying without string pattern would work.

I agree that is not very intuitive to have that parsed successfully when the pattern with 'T/Z' is not provided but fails when is provided. However, if you look into the documentation you'll notice that the Date/ Time formats provided there do not mention those tokens indeed.

BRs,

Lucas de Oliveira