System.Data.SQLite
View Ticket
Not logged in
Ticket UUID: a7d04fb1114ed36374a0f4e7f8b029a83922269f
Title: DateTime parameters are not bound with regards to DateTimeKind
Status: Closed Type: Incident
Severity: Important Priority: Medium
Subsystem: Convert Resolution: Fixed
Last Modified: 2015-02-19 01:05:52
Version Found In: 1.0.94.1
User Comments:
anonymous added on 2015-02-17 23:09:51:
Input parameters of type DateTime which have a DateTimeKind that is different from the database are not converted.

For example, if the database is set to Utc and a parameter is set to Local, System.Data.SQLite will NOT convert the value. Then once the value is read it will be set to Utc and will not match the original date.

To reproduce:

1) Set a parameter to a DateTime with a Kind that is different from the database Kind.
2) Input value and then read value back out of the database.
3) Dates will no longer match.

Or use the following test case (assuming local time zone != +0:00):

	[Test]
	internal void DateTimeParameterTest() {
		DateTime localcurrentdate = DateTime.Now;
		DateTime outputdate;

		SQLiteConnectionStringBuilder builder = new SQLiteConnectionStringBuilder();
		builder.DataSource = ":memory:";
		builder.DateTimeFormat = SQLiteDateFormats.Ticks;
		builder.DateTimeKind = DateTimeKind.Utc;

		using (SQLiteConnection cnn = new SQLiteConnection(builder.ToString())) {
			cnn.Open();

			using (DbCommand cmd = cnn.CreateCommand()) {
				cmd.CommandText = "SELECT ?";
				cmd.Parameters.Add(cmd.CreateParameter());
				cmd.Parameters[0].Value = localcurrentdate;

				using (DbDataReader reader = cmd.ExecuteReader()) {
					reader.Read();
					outputdate = reader.GetDateTime(0);
				}
			}
		}

		if (outputdate.ToLocalTime() != localcurrentdate)
			throw new Exception("Date in does not match date out.");
	}

mistachkin added on 2015-02-18 22:37:39:
Normally, the binding subsystem purposely avoids translating parameter values.
Also, this change would not be backward compatible.

mistachkin added on 2015-02-19 01:02:34:
Fixed on trunk via check-in [e5f6b68387].

mistachkin added on 2015-02-19 01:05:52:
Using the new BindDateTimeWithKind connection flag (set via the connection string)
will be required to activate the new DateTime conversion behavior, e.g.:

    using (SQLiteConnection source = new SQLiteConnection(
        "Data Source=test.db;Flags=BindDateTimeWithKind;"))
    {
      // more code here.
    }

Attachments: