Jun 29, 2017

SQLExplorer: Error parsing time

SQLite

I could not find any mention of this error with SQL Explorer (http://eclipsesql.sourceforge.net/)
but in some circumstances when using a DATETIME field - you can end up with an "Error parsing date" like this:

And it is not an issue with the SQLite driver (https://bitbucket.org/xerial/sqlite-jdbc/downloads/)

Assuming you have a table TEST:
CREATE TABLE TEST(TEST_ID INTEGER PRIMARY KEY AUTOINCREMENT,TEST_TIME DATETIME default "datetime('now')",TEST_LOG TEXT) ;
where you can add records like these:
insert into TEST (TEST_LOG) values ("TEST DATE ERROR PARSING")

If you attempt to do a simple select - you will get the error "error parsing date":
select TEST_ID, TEST_TIME TEST_DATE, TEST_LOG from TEST;
This command is perfectly valid under SQLite, or dBeaver, or another SQL client front end - but SQL Explorer (on Windows) may return this error parsing date.

Workaround

Now the workarounds:
select TEST_ID, strftime("%Y-%m-%d %H:%M:%S", TEST_TIME) TEST_DATE, TEST_LOG from TEST;

Another option is to use:
select TEST_ID, datetime(TEST_TIME) TEST_DATE , TEST_LOG from TEST;

No comments:

LinkWithin

Related Posts Plugin for WordPress, Blogger...