System.Data.SQLite
View Ticket
Not logged in
Ticket UUID: 04c7da847e458337846295f4d1b5b62bafd801cc
Title: Wrong query results on column containing DateTime serialized as ISO8601 string
Status: Closed Type: Incident
Severity: Important Priority: Medium
Subsystem: Convert Resolution: Works_As_Designed
Last Modified: 2017-04-08 17:58:24
Version Found In: 1.0.102
User Comments:
anonymous added on 2017-04-06 13:59:30:
I want UTC-DateTime values stored as text, using SQLiteDateFormats.ISO8601.
I don't provide an explicit DateTimeFormatString in the ConnectionString.

Values are therefore stored including milliseconds, i.e.:
Column "CreationDate" contains value "2017-04-06 13:59:03.400".

Perform a query "SELECT * FROM Jobs WHERE CreationDate >= @CreationDate" on the date column using a prepared statement leads to strange result:


FOR OPERATOR ">=":

No | Parameter                                                  | In result
---+------------------------------------------------------------+-----------
1  | new DateTime(2017, 4, 6, 13, 59, 3, 0, DateTimeKind.Utc)   | No
2  | new DateTime(2017, 4, 6, 13, 59, 3, 1, DateTimeKind.Utc)   | Yes
3  | new DateTime(2017, 4, 6, 13, 59, 3, 300, DateTimeKind.Utc) | Yes
4  | new DateTime(2017, 4, 6, 13, 59, 3, 400, DateTimeKind.Utc) | Yes
5  | new DateTime(2017, 4, 6, 13, 59, 3, 499, DateTimeKind.Utc) | Yes
6  | new DateTime(2017, 4, 6, 13, 59, 3, 500, DateTimeKind.Utc) | No

==> Unexpected results at No 1 and 5


FOR OPERATOR ">":

No | Parameter                                                  | In result
---+------------------------------------------------------------+-----------
1  | new DateTime(2017, 4, 6, 13, 59, 3, 0, DateTimeKind.Utc)   | No
2  | new DateTime(2017, 4, 6, 13, 59, 3, 1, DateTimeKind.Utc)   | Yes
3  | new DateTime(2017, 4, 6, 13, 59, 3, 300, DateTimeKind.Utc) | Yes
4  | new DateTime(2017, 4, 6, 13, 59, 3, 400, DateTimeKind.Utc) | No
5  | new DateTime(2017, 4, 6, 13, 59, 3, 499, DateTimeKind.Utc) | Yes
6  | new DateTime(2017, 4, 6, 13, 59, 3, 500, DateTimeKind.Utc) | No

==> Unexpected results at No 1 and 5

mistachkin added on 2017-04-08 17:58:24:
These results seem to be caused by treating DateTime values as strings.
Using the default format string results in string comparisons like:

    '2017-04-06 13:59:03.400' >= '2017-04-06 13:59:03Z'
    '2017-04-06 13:59:03.400' >= '2017-04-06 13:59:03.001Z'
    '2017-04-06 13:59:03.400' >= '2017-04-06 13:59:03.3Z'
    '2017-04-06 13:59:03.400' >= '2017-04-06 13:59:03.4Z'
    '2017-04-06 13:59:03.400' >= '2017-04-06 13:59:03.499Z'
    '2017-04-06 13:59:03.400' >= '2017-04-06 13:59:03.5Z'

The 'Z' is appended because the DateTime instances in question are UTC.

Using the following connection string property should help:

    "DateTimeFormatString=yyyy-MM-dd HH:mm:ss.FFFFFFF;"