|Title:||Entity Framework: DateTime format is not sortable|
|Last Modified:||2015-12-05 01:02:29|
|Version Found In:||220.127.116.11|
anonymous added on 2015-09-17 09:22:57:
The Entity Framework mapping stores DateTime values in a column of type datetime in the following format: yyyy-mm-dd hh:mm:ss.fffffff'Z'. The final Z indicates UTC time which I have set for my time values. The problem is that if there are trailing zeros in the sub-second part, they are dropped. This can lead to the following stored time values: 2015-09-17 09:19:20.123456Z 2015-09-17 09:19:20.1234567Z It is clear that the first line is earlier than the second, but when sorting, the following is the result for a string comparison: 2015-09-17 09:19:20.1234567Z 2015-09-17 09:19:20.123456Z This is the wrong sort order! When using this format, time values cannot be reliably sorted! Instead, all trailing zeros must be stored as well to keep the datetime value at a constant length. Mixing Z and non-Z values will of course also cause trouble but this is under the control of the developer who sets the data to write.
mistachkin added on 2015-09-17 18:08:45:
I'm not sure I understand the issue here. Where is the DateTime format string in question coming from?
mistachkin added on 2015-09-18 00:50:49:
If it's coming from the System.Data.SQLite assembly itself, those legacy format string cannot be changed without breaking backward compatibility. That being said, the DateTimeFormat and DateTimeFormatString connection string properties can be used to override the DateTime format handling on a per-connection basis.