Parsing timestamp string with date-time separator

[tnovosel]

Hello Exasol guys,

I have situation where  I need to parse timestamp string and

convert it to timestamp type in one column.

So string is. '2017-11-23T22:59:41.000' and input format I'm giving to TO_TIMESTAMP function is 'YYYY-MM-DDTHH24:MI:SS.FF3'.

I'm getting error :

org.jkiss.dbeaver.model.sql.DBSQLException: SQL Error [22007]: data exception - invalid datetime format (Session: 1682790266286220314)

But in this way without T separator it works:

SELECT TO_TIMESTAMP('2017-11-23 22:59:41.000','YYYY-MM-DD HH24:MI:SS.FF3');

Thank you,

Best regards,

Tom.

[mwellbro]

Hi Tom,

I´d say there is only a limited number of valid date/timestamp-formatstrings ( as mentioned in "Table 2.5. Elements of Date/Time format models" in the docs ).

A bit crude, but maybe this would be of use:

select to_timestamp(replace('2017-11-23T22:59:41.000','T',' '),'YYYY-MM-DD HH24:MI:SS.FF3');

Or do you not have control over your input value (i.e. gets passed from an application or some-such ? ) ?

Cheers
Malte

[exa-Chris]

I can see a "DBeaver" in the Error @Charlie can you come to the rescue here?

[Charlie]

nope, has nothing to do with DBeaver.

Exasol simply does not support this 😉

Had the same problem in the past and went the same way as @mwellbro 

[steffen_check24]

Hi Tom,

check issue: https://www.exasol.com/support/browse/EXASOL-2608.

The 'T' is supported from Exasol version 7 on.

I am working on version 7.0.3, it works that way for me, adding the T to the format string: 

SELECT TO_TIMESTAMP('2017-11-23 22:59:41.000','YYYY-MM-DDTHH24:MI:SS.FF3');

Cheers,

Steffen