Query on Datetime Fields with Milliseconds Gives Wrong Result in SQL Server

Query on datetime fields with milliseconds gives wrong result in SQL Server

SQL Server stores time part as number of 1/300 second long ticks from the midnight.

23:59:59.999 gets rounded to the nearest tick which happens to be 00:00:00.000 of the next day.

SELECT  CAST(CAST('2009-12-01 00:00:00.000' AS DATETIME) AS BINARY(8)),
        CAST(CAST('2009-12-01 23:59:59.997' AS DATETIME) AS BINARY(8)),
        CAST(CAST('2009-12-01 23:59:59.999' AS DATETIME) AS BINARY(8))

0x00009B8F 00000000    0x00009B8F 018B81FF    0x00009B90 00000000

In the first value, the date part, 0x9B8F (39823) is the number of days since Jan 1st, 1900, and the time part, 0, is the number of ticks since midnight.

In the second value, 0x018B81FF (25919999, or 24 * 60 * 60 * 300 - 1) is the maximal possible number of ticks since midnight.

Finally, the third value has the 0 in the time part and the date part increased by one.

Milliseconds wrong when converting from XML to SQL Server datetime

Yes, SQL Server rounds time to 3.(3) milliseconds:

SELECT CAST(CAST('2009-01-01 00:00:00.000' AS DATETIME) AS BINARY(8))
SELECT CAST(CAST('2009-01-01 00:00:01.000' AS DATETIME) AS BINARY(8))

0x00009B8400000000
0x00009B840000012C

As you can see, these DATETIME's differ by 1 second, and their binary representations differ by 0x12C, that is 300 in decimal.

This is because SQL Server stores the time part of the DATETIME as a number of 1/300 second ticks from the midnight.

If you want more precision, you need to store a TIME part as a separate value. Like, store time rounded to a second as a DATETIME, and milliseconds or whatever precision you need as an INTEGER in another columns.

This will let you use complex DATETIME arithmetics, like adding months or finding week days on DATETIME's, and you can just add or substract the milliseconds and concatenate the result as .XXXXXX+HH:MM to get valid XML representation.

Why is SQL Server losing a millisecond?

SQL Server only stores time to approximately 1/300th of a second. These always fall on the 0, 3 and 7 milliseconds. E.g. counting up from 0 in the smallest increment:

00:00:00.000

00:00:00.003

00:00:00.007

00:00:00.010

00:00:00.013

...

If you need that millisecond accuracy, there's no pleasant way around it. The best options I've seen are to store the value in custom number fields and rebuild it every time you fetch the value, or to store it as a string of a known format. You can then (optionally) store an 'approximate' date in the native date type for the sake of speed, but it introduces a conceptual complexity that often isn't wanted.

Converting a date in c# from SQL gives wrong milliseconds

This is because the dates are a timestamp (a number of milliseconds from a determined date in the past) to have the exact amount of milliseconds you need to have the exact time in the database server and the machine you are running the c# application.

SQL Millisecond Precision

Values in the legacy datetime datatype are only stored to approx 1/300 of a second precision.

valid ms endings are 0,3,7 anything else will be rounded to the nearest of those (5 is equidistant between 3 and 7 and is rounded up to 7, this is the only 2ms adjustment).

So .242 is rounded up to .243 when implicitly cast to datetime

If you are on SQL Server 2008+ you can use datetime2(3) for full ms precision.

Or just use

DELETE FROM MyTable WHERE MyDate < '2015-06-24 16:32:42.243'

SQL Server : DATEADD() not working beyond milliseconds when using a string value

Because you've supplied a literal string, and so DATEADD converts the value to it's default data type, datetime. A datetime is accurate to 1/300th of a second and a value like 2021-11-01 05:10:00.0000000 therefore is too large; it has an accuracy greater than 1/300th of a second.

If you explicitly converted the value to a date and time data type that goes up to 1/1000000 of a second, it would work:

SELECT DATEADD(HOUR,4,CONVERT(datetime2(7),'2021-11-01 05:10:00.0000000'))

Milliseconds not showing in SQL output for DateTime type

So lets assume you have those values in your database:

By running pure sql statement you should be able to retrieve datetime in the format you would anticipate, with milliseconds

And in your C#, as an example, you can control the format, here is an example:

            var res = db.MyTables.Take(100);

            foreach(var date in  res)
            {
                Console.WriteLine(Convert.ToDateTime(date.EventDateTime).ToString("dd/MM/yyyy hh:mm:ss.fff"));
            }

Here is the output:

No milliseconds value when reading DateTime values from a SQL database in C#

Here's how I'd try to troubleshoot this:

1. step through in the debugger and look at the type and value of reader[Timestamp"]. If the type is not SqlDateTime, that would make me suspicious-- I'd then look at the query to figure out why that column was returning another type instead of DATETIME (or DATETIME2, etc.)

2. if that value is SqlDateTime and it does contains milliseconds, then I'd look at the cast as the source of the problem. To validate this, I'd try (in debugger or code) SqlDataReader.GetDateTime() and SqlDataReader.GetSqlDateTime() to see if either returns the correct result. This admittedly seems really unlikely as source of the problem-- casting should work fine.

3. if that value from #1 is SqlDateTime but contains no milliseconds, then I'd look to an upstream problem in the database-- in other words your query is returning something without milliseconds. when you execute the exact same query in Management Studio, do you see milliseconds?

My guess is this is a query-related issue. But am intrigued to find our more.

---

Related Topics