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;

SQLite: Loop simplified

As I was discussing this last tip using a simile loop in SQLite to populate data entry http://blogmymix.blogspot.com/2017/06/sqlite-using-table-to-loop.html

A comment was made that the tRange table was not absolutely necessary
as the following command would suffice:

insert into tTarget(i)  select ('My test without tRange: ' || tIndex.id)  from tIndex where tIndex.id <=10000 ;

or with proper formatting

insert into tTarget(i)  
  select ('My test without tRange: ' || tIndex.id)  
  from tIndex where tIndex.id <=10000 ;

SQLite: Using a table to loop

Loop

You want to create a table with lots of records for testing purposes - in SQL you could make a loop using something like this:

while (@i <= 10000)
 begin
  insert into table1 values ( 'field1' + cast (@i as char).
 'field2'+ cast (@i as char).
...
)
set @i+=1
end
go

Unfortunately in SQlite loops are not implemented - but you could use this trick instead
It will use a tIndex table, and a tRange table to generate some records in a tTarget table.

The tTarget table  is for the result:
create table target (i text);

This table will receive the result as text

tRange table

create table tRange (start, finish);
insert into tRange (1, 100) ;

And then afterward you can change the start and finish parameters using:
update tRange set start = 3 ;
update tRange set finish = 90 ;

tIndex table

This one is tricky because it has to contain as many records as possible - you will have to create this table via a Java program (see previous post) - by default there will be 10,000 records.

Now the Loop trick 

insert into tTarget(i)
select ('Record number '||tIndex.id)
from tIndex join tRange
on (tIndex.id >= tRange.start and tIndex.id <= tRange.finish) ;

This will generate Record Number + start ...  up to Record Number+ finish

Java: Creating a table with 10,000 records in SQLite