System.Data.SQLite

If I set DateTime format to ISO8601 and show the table data in Visual Studio 2008, the values are shown correctly.

If I choose Julian or Ticks, the DateTime values are not shown, "<Unable to read data>" is shown instead.

Is there any possibility to add this feature? I think most people will use the Julian or Ticks format (8 bytes compared to 19 in ISO8601).

Thanks,
Petr

The DateTimeFormat is a helper that tells the SQLite provider "hey if you see a column marked as a date/time field, use this method to parse it into a DateTime automatically".

The fact that setting it to ISO8601 works and Ticks/Julian doesn't work tells me that the datetime data in the columns in your database are formatted as text and stored in ISO8601 format.

If you want to use Ticks or Julian, you will have to not only set that in the connection string, but you will also have to make sure all the existing data in your database is in that format, and that all subsequent inserts/updates to those columns are using parameterized queries using DateTime objects.  Alternatively, if you build your SQL statements yourself, then you need to makes sure you parse those columns into the correct format when you insert/update so the SQLite.NET provider can translate them when reading the data back out.

Robert

Thanks Robert,

I think I make everything as you say:

1) In the connection string, I set SQLiteConnectionStringBuilder.DateTimeFormat to ISO8601, JulianDay or Ticks.

2) Create a new database

3) Create new table with a "TIMESTAMP" column

4) Add some rows using parametrized queries passing a DateTime, so everything is managed by the .NET provider.

Now if I open the table in the Server Explorer in VS2008, the values are correctly displayed only when stored in ISO8601 format. In JulianDay and Ticks format, Server Explorer displays "<Unable to read data>" instead of values in the DateTime column. All other columns (integers, doubles,...) are displayed correctly.

If I open the database using sqlite3.exe, the DateTime values seem to be right (i.e. integer value for Ticks and some double value for JulianDay). Of course, if I display the table in my code using DataGridView, everything is OK as well.

Thanks for response,
Petr

Robert,

any update about this? Or does it mean that Julian/Ticks datetime formats are not supported in VS?

Thanks,
Petr

The same connection string is used in Visual Studio as in your program?

You'll have to be patient on me investigating bugs at the moment.  I've been pretty busy the last few months getting a new company off the ground.

Robert,

my appologies and thanks for your idea...I have just clicked the magic "Advanced" button in VS and set DateTimeFormat to JulianDay. Now everything works perfect!

I wish you all best for you and your company. Your .NET provider is an excellent piece of work, so there I have no worries about you. 

Thanks and sory again for my fault!