Datetime format for SQLite

[verbalins]

Hello,

I have a problem when using the SQLite interface. In my database I have a table called Orders with a column formatted as datetime which is always formatted to YYYY-MM-DD HH:MMS.SSSS as the only option.

In Plant Simulation when trying to run a query to import this database into a table with

SQLite.sql(testTable, "SELECT * FROM Orders);

I run into a problem. Due to Plant Simulation only recognizing YYYY/MM/DD or YYYY.MM.DD as datetime formats the column in the Plant Simulation table is not automatically set to datetime. My understanding of the problem is that since Plant Simulation only supports certain formats and with '-' isn't one of them this is not possible?

Or am I misunderstanding this issue? Would it be possible to also add YYYY-MM-DD as a datetime format in Plant Simulation?

[RalfTobel · Accepted solution]

Hi verbalins,

you can use a select statement like the following:

select strftime('%d.%m.%Y %H:%M:%S', x) from test;

Here the strftime function of SQLite is used to convert the date stored in column 'x' in the table test to the format Plant Simulation understands:

Regards,

Ralf

[verbalins]

Thanks for the reply Ralf!

That was the solution I came up with ('%Y/%m/%d %H:%M:%S.%f').

But it’s not really optimal, since Plant Simulation doesn't recognize the format. Then I still will have to convert the column through setDataType(column, "DateTime"). Seeing as the other formats, like integer and text is possible to convert automatically with the sql method.

But this is something that won't be possible in Plant Simulation automtically? I have the same issue with locally formatted dates as well in Excel. They also use '-' instead of '/' or '.'. In Excel it's of course easier to change.

[RalfTobel]

Right, you have to set the data type manually.

SQLite has no data type for dates. It can be stored in integer, float or text columns.

So there is no way for Plant Simulation to detect that it is a date column.

[verbalins]

Ah, now I see the reason why. Thank you very much Ralf!