08-11-2020 12:59 PM
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.
16-11-2020 08:20 AM
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
09-11-2020 07:01 PM
I can see a "DBeaver" in the Error @Charlie can you come to the rescue here?
11-11-2020 09:16 AM
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
08-11-2020 02:29 PM
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
Then it's time to become part of a unique family! Discover helpful tips and support other Community members with your knowledge.
Sign In