System.Data.SQLite

Assume a SQLiteConnection with a connection string of "data source=test.sqlite;datetimeformat=JulianDay;datetimekind=Utc" on Win 10 and .NET FW 4.

Inserting a row to a table with a DateTime-Parameter for a column writes this DateTime as JulianDay-Double into the column. 
Reading this column of that row back may result in a DateTime value which is sometimes 1ms less then the original value. An example DateTime is 2016-11-20 00:46:03.000 (or any other date with the same time of day).

The reason:
1) Not every (finite decimal) time of day fraction can be stored on a (binary finite) floating point value. In such cases some of the available (binary finite) neighbours are used to approximate the time of day fraction.
2) DateTime values are stored and *recreated* only with millisecond precision in SQLiteConvert

Here the code-example from a C# console project:

        public static void copyPaste()
        {
            SQLiteConnection.CreateFile("test.sqlite");
            using (var lConn = new SQLiteConnection("data source = test.sqlite;datetimeformat = JulianDay;datetimekind = Utc")) {
                lConn.Open();
                using (var lCreateCmd = lConn.CreateCommand())
                {
                    lCreateCmd.CommandText = @"CREATE TABLE [Test] (dt)";
                    lCreateCmd.ExecuteNonQuery();
                }
                using (var lInsertCmd = lConn.CreateCommand())
                {
                    lInsertCmd.CommandText = @"INSERT INTO [Test](dt) VALUES(@dt)";
                    var dt = lInsertCmd.CreateParameter();
                    dt.ParameterName = "dt";
                    dt.Value = new DateTime(2016, 11, 20, 00, 46, 03, 000, DateTimeKind.Utc);
                    lInsertCmd.Parameters.Add(dt);
                    lInsertCmd.ExecuteNonQuery();
                }
                using (var lSelectCmd = lConn.CreateCommand())
                {
                    lSelectCmd.CommandText = @"SELECT dt FROM [Test]";
                    using (var lReader = lSelectCmd.ExecuteReader())
                    {
                        while (lReader.Read())
                        {
                            Console.WriteLine("Value: {0:yyyy-MM-dd HH:mm:ss.ffff}", lReader.GetDateTime(0));
                        }
                    }
                }
            }
        }

But:
As long as the Julian Day Number is not too large, the JulianDay-Double can store values to a precision of at least 1/10 msec (100usec). Therefore it is possible to restore the original DateTime value with millisecond precision using the sub-ms fraction of the JulianDay-Double.

The proposed solution System.Data.SQLite.SQLiteConvert.cs (row 259):
        private static long DoubleToJd(
            double julianDay
            )
        {
            return (long)Math.Round(julianDay * 86400000.0);
        }

So, using Math.Round will result in the desired behavior?  I'll need to spend
some time understanding the corner cases impacted by this change.

As long as SQLiteConvert only store a DateTime value with millisecond precision (with the julian day conversion, see SQLiteConvert.computeJD) the conversion back to a DateTime should yield the original value. 

The following methods in SQLiteConvert are of interest for this case:
long DoubleToJd(double julianDay)
double JdToDouble(long jd)
long computeJD(DateTime dateTime)


C# Interactive
> // Julian-Day-Number of 2016-11-27 * 86400000 + 00:46:03.000 in msec since 00:00
> long sjd = 212347010763000;
> long ljd = (long)((sjd / 86400000.0) * 86400000.0);
> Console.WriteLine(ljd)
212347010762999

I was able to reproduce the issue locally.

Fixed on branch via check-in [471c16c6171c9fee].

Fix merged to trunk via check-in [f154173793f81c6f].