The driver for JDBC is from http://www.zentus.com/sqlitejdbc/, current newest version is v056 based on Sqlite 3.6.14.2.

The DotNet driver is from http://sqlite.phxsoftware.com/, current newest version is 1.0.65.0 based on Sqlite 3.6.16

I have only checked the embedded version of Sqlite, for the CS version, I think the behaviors in Java and DotNet is same, because the database server generated the date type in the same way.

OK, let’s check the sample and steps:

1. Using Java source code to create a new database named “java.sqlite.db” and create a simple table named “testtable” which only contains one column named “datefield” with data type “datetime”. And then insert some data rows into the table using JDBC.
2. Using C# source code to do the same step except the name of database is “dotnet.sqlite.db” and inserting using ADO.Net.
3. Using JDBC to retrieve data from “dotnet.sqlite.db”, and show them on console window. It works, but the date is not correctly, the values obviously larger than what we have inserted in the table.
4. Using ADO.Net to retrieve data from “java.sqlite.db” and show then on console window. Unfortunately, we got some exceptions indicates the “data type incorrect”.

After investigation, I found both drivers are using “long” (INT64) to store the date value into table, but the same date in Java and DotNet are quite different, the long value in DotNet is greater than in Java. The reason is all of the drivers store the long value using a relative milliseconds after a specified date, but Java is using “1970-01-01 00:00:00” and DotNet using “0001-01-01 00:00:00” , thus the number in DotNet is greater than Java.

The milliseconds amount from “0001-01-01 00:00:00” to “1970-01-01 00:00:00” is 621355968000000000.

To resolve this issue, we should add 621355968000000000 to the Java-date when used it in DotNet, and vice versa.

No source code for you, because it is quite easy.

Here is a refer documentation : http://blog.csdn.net/laobai_2006/archive/2008/10/22/3124719.aspx