Parsing timestamp string with date-time separator

tnovosel
Contributor

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.

4 REPLIES 4

steffen_check24
Contributor

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

exa-Chris
Community Manager
Community Manager

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

Connecting Customers, Partners, Prospects and Exasolians is my passion. Apart from that I cycle, listen to music, and try to understand what all those technical discussions really mean...

Charlie
Xpert

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 

mwellbro
Xpert

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