System.Data.SQLite

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.

I'm not sure I understand the issue here.  Where is the DateTime format string
in question coming from?

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.