| Ticket Hash: | 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: (text/x-fossil-plain) 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;"
| ||||
