Trouble with DateTime

[mjegen]

I recently upgraded to version 4.4 from version 3.6 and started seeing some strange behaviour with regards to the DateTime data type.

When I insert date values into a SQLite database using an unparameterized query everything works as expected. However, if I use a parameterized query then a slightly different value for the DateTime is inserted into the database.

Here is a code sample that demonstrates the issue:

Code: Select all

        
static void Main(string[] args)
{
    var cs = new SQLiteConnectionStringBuilder();
    cs.FailIfMissing = false;
    cs.DataSource = "test.db";

    var cnn = new SQLiteConnection(cs.ConnectionString);
    cnn.Open();

    var cmd = cnn.CreateCommand();
    cmd.CommandText = "CREATE TABLE IF NOT EXISTS TestTable (SomeText TEXT, SomeDateTime DATETIME)";
    cmd.ExecuteNonQuery();

    cmd.CommandText = "INSERT INTO TestTable VALUES ('test', '2000-01-01')";
    cmd.ExecuteNonQuery();

    // this works - INSERT worked as expected
    cmd.CommandText = "SELECT COUNT(*) FROM TestTable WHERE SomeDateTime = '2000-01-01'";
    var rowCount = Convert.ToInt32(cmd.ExecuteScalar());
    Debug.Assert(rowCount == 1);

    cmd.CommandText = "DELETE FROM TestTable";
    cmd.ExecuteNonQuery();

    cmd.CommandText = "INSERT INTO TestTable VALUES (:p1, :p2)";
    cmd.Parameters.Add(new SQLiteParameter("p1", "text"));
    cmd.Parameters.Add(new SQLiteParameter("p2", new DateTime(2000, 1, 1)));
    cmd.ExecuteNonQuery();

    // this fails - parameterized INSERT did not write the expected value in the DateTime column
    cmd.CommandText = "SELECT COUNT(*) FROM TestTable WHERE SomeDateTime = '2000-01-01'";
    rowCount = Convert.ToInt32(cmd.ExecuteScalar());
    Debug.Assert(rowCount == 1);

    // this works
    cmd.CommandText = "SELECT COUNT(*) FROM TestTable WHERE date(SomeDateTime) = '2000-01-01'";
    rowCount = Convert.ToInt32(cmd.ExecuteScalar());
    Debug.Assert(rowCount == 1);
}

[Pinturiccio]

We have reproduced the issue. We will investigate it and notify you about the results as soon as possible.

[Pinturiccio]

We have changed the behavior in dotConnect for SQLite 4.5.192: when an object of the SQLiteDateTime type with the property IsDate = true is a parameter value, the value passed to the database has the Date format.

The new build of dotConnect for SQLite 4.5.192 is available for download now!
It can be downloaded from http://www.devart.com/dotconnect/sqlite/download.html (trial version) or from Registered Users' Area (for users with valid subscription only).

An SQLiteDateTime type object created using a constructor, where only year, month and day are specified, automatically gets the IsDate property value equal to 'true'.

For the sake of correct work, you should replace this code:

Code: Select all

cmd.Parameters.Add(new SQLiteParameter("p2", new DateTime(2000, 1, 1)));

With the following one:

Code: Select all

cmd.Parameters.Add(new SQLiteParameter("p2", new SQLiteDateTime(2000, 1, 1)));

[mjegen]

I installed version 4.5.192 but the code sample I provided will still not execute correctly.

If I use the SQLiteDateTime parameter then I need to explicitly set IsDate = true for the code sample to work.

Code: Select all

cmd.Parameters.Add(new SQLiteParameter("p2", new DateTime(2000, 1, 1)));

becomes:

Code: Select all

var dateTime = new SQLiteDateTime(new DateTime(2000, 1, 1));
dateTime.IsDate = true;
cmd.Parameters.Add(new SQLiteParameter("p2", dateTime));

[mjegen]

It looks like something changed in the providers that increased the amount of precision being stored when writing DateTime objects to the database.

If I change my sample so that the query to get the results includes enough precision then it works correctly.

Code: Select all

DateTime column
cmd.CommandText = "SELECT COUNT(*) FROM TestTable WHERE SomeDateTime = '2000-01-01 00:00:00.0000000'";

Previously, our code was only using 4 decimals of precision when generating the date string from the DateTime object for comparison purposes. Something seems to have changed either in the SQLite engine or the provider so that it now writes 7 decimals of precision when storing a DateTime value.

[Pinturiccio]

If I use the SQLiteDateTime parameter then I need to explicitly set IsDate = true for the code
sample to work.

If you pass an object with the DateTime type to the constructor of an object with the SQLiteDateTime type, you should explicitly assign the True value to the IsDate property, just as you have done.
If you use a SQLiteDateTime object constructor, to which three integers are passed (year, month and day), the IsDate property equals True.

It looks like something changed in the providers that increased the amount of precision being
stored when writing DateTime objects to the database.

Yes, at our users' requests, we increased precision of fractional seconds during passing DateTime to the database from 4 to 7 digits after the delimiter. At the same time, we didn't change precision of fractional seconds for the SQLiteDateTime type.

Starting from the next build, behavior will be changed and fractional seconds precision will be increased to 6 when working with the datetime type. These changes are connected with peculiarities of internal implementation. We will notify you when the corresponding build of dotConnect for SQLite is available for download.

[Pinturiccio]

The new build of dotConnect for SQLite 4.5.192 is available for download now!
It can be downloaded from http://www.devart.com/dotconnect/sqlite/download.html (trial version) or from Registered Users' Area (for users with valid subscription only).
For more information, please refer to http://forums.devart.com/viewtopic.php?t=26196