Why is C# DateTime.Now/DateTime.UtcNow ahead of SQL Server's SYSUTCDATETIME()/SYSDATETIME() even though C# code executes before the SQL Query

Asked 25/7 at 11:17 Answered 28/7 at 4:4

Solved c#.net sql-server datetime .net-core

I want to know the reason why my C# date is larger than the SQL date even though the C# code is running first and after that the SQL query,

Logically the SQL date should be greater than C# date.

For your reference the .NET application and SQL Server are on my local machine.

C# Code:


using System.Data;
using System.Data.SqlClient;

for (int i = 1; i <= 20; i++)
{
    AddRecord();
}
Console.WriteLine("20 records added in database....");

void AddRecord()
{
    try
    {
        string ConnectionString = @"data source=OM5\SQL2019; database=TestDb; integrated security=SSPI";
        using (SqlConnection connection = new SqlConnection(ConnectionString))
        {
            SqlCommand cmd = new SqlCommand()
            {
                CommandText = "SP_AddRecord",
                Connection = connection,
                CommandType = CommandType.StoredProcedure
            };

            SqlParameter param1 = new SqlParameter
            {
                ParameterName = "@CSharp_DateNow",
                SqlDbType = SqlDbType.DateTime2,
                Value = DateTime.Now,
                Direction = ParameterDirection.Input
            };
            cmd.Parameters.Add(param1);

            SqlParameter param2 = new SqlParameter
            {
                ParameterName = "@CSharp_DateUTCNow",
                SqlDbType = SqlDbType.DateTime2,
                Value = DateTime.UtcNow,
                Direction = ParameterDirection.Input
            };
            cmd.Parameters.Add(param2);

            connection.Open();
            cmd.ExecuteNonQuery();
        }
    }
    catch (Exception ex)
    {
        Console.WriteLine($"Exception Occurred: {ex.Message}");
    }
}

SQL:

CREATE TABLE [dbo].[Records](
    [Id] [int] PRIMARY KEY IDENTITY(1,1) NOT NULL,
    [SQL_SysDateTime] [datetime2](7) NOT NULL,
    [CSharp_DateNow] [datetime2](7) NOT NULL,
    [SQL_SysUTCDateTime] [datetime2](7) NOT NULL,
    [CSharp_DateUTCNow] [datetime2](7) NOT NULL
)

CREATE OR ALTER   PROCEDURE [dbo].[SP_AddRecord]
@CSharp_DateNow datetime2,
@CSharp_DateUTCNow datetime2
AS
BEGIN
    SET NOCOUNT ON;

     insert into Records(SQL_SysDateTime, CSharp_DateNow, SQL_SysUTCDateTime, CSharp_DateUTCNow) values
     (SYSDATETIME(),@CSharp_DateNow,SYSUTCDATETIME(),@CSharp_DateUTCNow)
END

Result in table

SQL_SysDateTime	CSharp_DateNow	Diff. (MS)	SQL_SysUTCDateTime	CSharp_DateUTCNow	Diff. (MS)
2024-07-26 13:26:35.2898391	2024-07-26 13:26:34.9701658	319	2024-07-26 07:56:35.2898391	2024-07-26 07:56:34.9726788	317
2024-07-26 13:26:35.3054610	2024-07-26 13:26:35.3174393	-12	2024-07-26 07:56:35.3054610	2024-07-26 07:56:35.3174492	-12
2024-07-26 13:26:35.3210815	2024-07-26 13:26:35.3217354	0	2024-07-26 07:56:35.3210815	2024-07-26 07:56:35.3217461	0
2024-07-26 13:26:35.3210815	2024-07-26 13:26:35.3261818	-5	2024-07-26 07:56:35.3210815	2024-07-26 07:56:35.3261915	-5
2024-07-26 13:26:35.3367030	2024-07-26 13:26:35.3310309	5	2024-07-26 07:56:35.3367030	2024-07-26 07:56:35.3310384	5
2024-07-26 13:26:35.3367030	2024-07-26 13:26:35.3411312	-5	2024-07-26 07:56:35.3367030	2024-07-26 07:56:35.3411394	-5
2024-07-26 13:26:35.3367030	2024-07-26 13:26:35.3418632	-5	2024-07-26 07:56:35.3367030	2024-07-26 07:56:35.3418676	-5
2024-07-26 13:26:35.3367030	2024-07-26 13:26:35.3430069	-7	2024-07-26 07:56:35.3367030	2024-07-26 07:56:35.3430104	-7
2024-07-26 13:26:35.3367030	2024-07-26 13:26:35.3437519	-7	2024-07-26 07:56:35.3367030	2024-07-26 07:56:35.3437554	-7
2024-07-26 13:26:35.3367030	2024-07-26 13:26:35.3446140	-8	2024-07-26 07:56:35.3367030	2024-07-26 07:56:35.3446172	-8
2024-07-26 13:26:35.3367030	2024-07-26 13:26:35.3452865	-9	2024-07-26 07:56:35.3367030	2024-07-26 07:56:35.3452894	-9
2024-07-26 13:26:35.3367030	2024-07-26 13:26:35.3459309	-9	2024-07-26 07:56:35.3367030	2024-07-26 07:56:35.3459336	-9
2024-07-26 13:26:35.3367030	2024-07-26 13:26:35.3466520	-10	2024-07-26 07:56:35.3367030	2024-07-26 07:56:35.3466552	-10
2024-07-26 13:26:35.3367030	2024-07-26 13:26:35.3475280	-11	2024-07-26 07:56:35.3367030	2024-07-26 07:56:35.3475305	-11
2024-07-26 13:26:35.3367030	2024-07-26 13:26:35.3486445	-12	2024-07-26 07:56:35.3367030	2024-07-26 07:56:35.3486474	-12
2024-07-26 13:26:35.3367030	2024-07-26 13:26:35.3492964	-13	2024-07-26 07:56:35.3367030	2024-07-26 07:56:35.3492991	-13
2024-07-26 13:26:35.3367030	2024-07-26 13:26:35.3501936	-14	2024-07-26 07:56:35.3367030	2024-07-26 07:56:35.3501961	-14
2024-07-26 13:26:35.3367030	2024-07-26 13:26:35.3506370	-14	2024-07-26 07:56:35.3367030	2024-07-26 07:56:35.3506392	-14
2024-07-26 13:26:35.3367030	2024-07-26 13:26:35.3511339	-15	2024-07-26 07:56:35.3367030	2024-07-26 07:56:35.3511362	-15
2024-07-26 13:26:35.3367030	2024-07-26 13:26:35.3517053	-15	2024-07-26 07:56:35.3367030	2024-07-26 07:56:35.3517087	-15

I want an actual reason or an authentic source which can explain this.

Ellanellard answered 25/7 at 11:17 Comment(15)

Presumably the times on the host you are running the application on and the host hosting SQL Server are different, so you get different values. – Cosset 25/7 at 11:19

@ThomA - The OP said they were the same computer. – Nigel 25/7 at 11:21

I saw that, @Enigmativity, but they also use the connection string OM5\SQL2019, which implies that they are not the same device (as they should be using .\SQL2019 if it's the local device). I've also been "fooled" enough times to always questions when a user says they are using the same device, when they actually aren't. – Cosset 25/7 at 11:24

even if you are calling them at the same time, CPU generally have a scheduler that will create a queue internally. sometimes SQL will get a thread first, sometimes C# will get it first, some times both will get separate threads at the same time. – Graveclothes 25/7 at 11:25

As an side note as well, the prefix sp_ is reserved, by Microsoft, for Special / System Procedures. It should not be used for User Procedures. Doing so comes with a performance cost and the risk of your Procedure simply not working one day after an update/upgrade. Either use a different prefix or (possibly better) no prefix at all. Is the sp_ prefix still a no-no? – Cosset 25/7 at 11:26

@ShamvilKazmi I have also verified this multiple time and also different machine but the result is same – Ellanellard 25/7 at 11:27

@ThomA - Fair enough. You make sense. – Nigel 25/7 at 11:28

@ShamvilKazmi the C# code must run before the SQL because it's providing the value to SQL. – Cohette 25/7 at 11:29

Where you noted a difference of -4, the difference is actually -3 – Goldstone 25/7 at 11:29

@ThomA I want to add one more thing same thing i does with by creating Node Js API and Using same proc but it giving correct time. Their JavaScript time is lesser than Sql time. That is also correct. – Ellanellard 25/7 at 11:34

i'm guessing some sort of rounding is going on when translating c# datetime to sql datetime which makes it round wrongly. Can you create a table with datetime2 and post the results of that? SYSUTCDATETIME can be used for default – Housecarl 25/7 at 11:44

@Shivam you say you used datetime2 but never posted such code. All discrepancies except 3 can be explained by datetime's accuracy and nothing posted in the question shows otherwise. No CREATE TABLE using datetime2(7), no stored procedure with a datetime2(7) parameter and SYSUTCDATETIME default, no C# code using SqlDbType.DateTime2. The 3 remaining cases can be startup, connection or network delays. You've already got an answer to the question. – Younglove 25/7 at 12:3

i was able to replicate this issue in my system. diff in MICROSECOND 98650 526 -835 -261 -666 -138 -553 -124 -618 -110 -508 -910 -191 -608 -98 -540 -94 -528 -940 -144 – Graveclothes 25/7 at 13:29

@Shivam - It's opened again. – Nigel 25/7 at 23:6

@DaleK Yes me to – Ellanellard 26/7 at 12:56

These values are precise but not accurate.

If you take the distinct values from SQL_SysDateTime and compare them...

SELECT MS_Diff = DATEDIFF(NANOSECOND, '2024-07-26 13:26:35.2898391', '2024-07-26 13:26:35.3054610')/1E6, 
       MS_Diff = DATEDIFF(NANOSECOND, '2024-07-26 13:26:35.3054610', '2024-07-26 13:26:35.3210815')/1E6, 
       MS_Diff = DATEDIFF(NANOSECOND, '2024-07-26 13:26:35.3210815', '2024-07-26 13:26:35.3367030')/1E6

This returns 15.6219, 15.6205, 15.6215 as differences between them (in ms).

As documented here SQL Server uses GetSystemTimeAsFileTime() for SYSDATETIME()/SYSUTCDATETIME().

Raymond Chen indicates here that by default GetSystemTimeAsFileTime() is not especially accurate though mentions default refresh periods for the value returned by it of 55ms or 10ms rather than 15.62 so presumably this has changed since then.

Various sites indicate that the default timer resolution in Windows 10 is 15.6 ms (or more specifically 15625000ns) so the above gaps are in line with that.

For C# the documentation for DateTime.UtcNow doesn't look any more promising

The resolution of this property depends on the system timer, which depends on the underlying operating system. It tends to be between 0.5 and 15 milliseconds.

So there is still the question as to how that is achieving the greater accuracy.

You have tagged .NET core. Per this pull request it now calls GetSystemTimePreciseAsFileTime when available (one of the later ones mentioned in the Raymond Chen post above).

On my local machine (Win 11) I do mostly see diffs of around 1ms when running the following test. (But running powercfg -energy does tell me that various processes I have running (including chrome.exe and MongoDB) have requested a low time interval for the Platform Timer Resolution)

SET NOCOUNT ON;

DECLARE @Times TABLE(insert_time datetime2)

DECLARE @Counter INT = 0

WHILE @Counter < 10000
BEGIN
INSERT @Times VALUES (SYSUTCDATETIME())
SET @Counter+=1;
END


SELECT  [rowcount] = COUNT(*), 
        insert_time, 
        prev_insert_time = LAG(insert_time) OVER (ORDER BY insert_time),
        diff_ms = DATEDIFF(NANOSECOND,LAG(insert_time) OVER (ORDER BY insert_time), insert_time)/1e6
FROM @Times
GROUP BY insert_time

SQL Server doesn't currently have any native way of calling GetSystemTimePreciseAsFileTime and returning datetime2(7) so if this is important to you you will need to do it outside of the database (you could also use CLR integration for this but then the assembly would need to be marked as unsafe to invoke the WinAPI function).

Running the above on Azure SQL database I got the following results so doesn't look like it is refreshed any more frequently there (and you only get ~64 unique values per second).

interestingly replacing SYSUTCDATETIME() with GETUTCDATE() I do get diffs of 3.3333/3.3334 ms so this does appear less precise but more accurate. Presumably this as a result of the "correction" mentioned here.

This situation appears to me to be less than ideal. There is a feedback request Have SYSDATETIME() return value from GetSystemTimePreciseAsFileTime() but it only has 3 votes and is tagged "Archived" so not sure if that means that it will never be considered.

rowcount	insert_time	prev_insert_time	diff_ms
563	2024-07-29 07:21:30.6607494	NULL	NULL
646	2024-07-29 07:21:30.6763740	2024-07-29 07:21:30.6607494	15.6246
659	2024-07-29 07:21:30.6919988	2024-07-29 07:21:30.6763740	15.6248
673	2024-07-29 07:21:30.7076257	2024-07-29 07:21:30.6919988	15.6269
666	2024-07-29 07:21:30.7232517	2024-07-29 07:21:30.7076257	15.626
659	2024-07-29 07:21:30.7390662	2024-07-29 07:21:30.7232517	15.8145
667	2024-07-29 07:21:30.7545026	2024-07-29 07:21:30.7390662	15.4364
660	2024-07-29 07:21:30.7701262	2024-07-29 07:21:30.7545026	15.6236
667	2024-07-29 07:21:30.7857507	2024-07-29 07:21:30.7701262	15.6245
668	2024-07-29 07:21:30.8013755	2024-07-29 07:21:30.7857507	15.6248
664	2024-07-29 07:21:30.8169996	2024-07-29 07:21:30.8013755	15.6241
631	2024-07-29 07:21:30.8326241	2024-07-29 07:21:30.8169996	15.6245
660	2024-07-29 07:21:30.8482510	2024-07-29 07:21:30.8326241	15.6269
662	2024-07-29 07:21:30.8638744	2024-07-29 07:21:30.8482510	15.6234
670	2024-07-29 07:21:30.8795009	2024-07-29 07:21:30.8638744	15.6265
185	2024-07-29 07:21:30.8951255	2024-07-29 07:21:30.8795009	15.6246

Vienne answered 28/7 at 4:4 Comment(8)

But given C# and SQL are reading the same system clock, how can SQL be showing a lower value than C#? I understand the resolution is at 15ms, but surely they should be reading the same value? And how can SQL be showing the same value for multiple calls to the same SP? I guess thats possible if they are all within the same 15ms interval? – Cohette 28/7 at 4:15

Yes it is very expected that SQL will return the same values if called before the time is refreshed. .NET presumably uses some other method behind DateTime.UtcNow than calling GetSystemTimeAsFileTime. Maybe one of the later ones mentioned in Raymond Chen's post – Vienne 28/7 at 4:20

OK that makes sense, do you want to summarise that at the end of your question e.g. "SQL Server appears to be using the default 15ms refresh time as can be seen by the fact that its value doesn't change for multiple calls. Whereas .NET appears to be using one of the more accurate methods and therefore slowly moves ahead of SQL until the end of the refresh period". – Cohette 28/7 at 4:23

though .NET actually isn't documented to be any more accurate

The resolution of this property depends on the system timer, which depends on the underlying operating system. It tends to be between 0.5 and 15 milliseconds.

learn.microsoft.com/en-us/dotnet/api/… – Vienne 28/7 at 4:23

Thats a bit odd thinking about it, 15ms isn't a very high resolution if you're firing lots of updates into SQL. – Cohette 28/7 at 4:23

Weird then that .NET claims to be the same... but the behaviour appears to say otherwise. Its at least a good theory :) – Cohette 28/7 at 4:24

Looks like .NET core made an improvement in this area github.com/dotnet/coreclr/pull/9736 – Vienne 28/7 at 4:29

Good find! Two times slower though... have to call it less :) – Cohette 28/7 at 4:31

You need to use SqlDbType.DateTime2 as it provides higher accuracy.

From the docs

DateTime - Date and time data ranging in value from January 1, 1753 to December 31, 9999 to an accuracy of 3.33 milliseconds. (most of your discrepancy results are in the 3-4 range)

DateTime2 - Date and time data. Date value range is from January 1,1 AD through December 31, 9999 AD. Time value range is 00:00:00 through 23:59:59.9999999 with an accuracy of 100 nanoseconds.

Sulcus answered 25/7 at 11:32 Comment(3)

And sysdatetime because getdate returns a datetime. And datetime2 in the proc also. – Cohette 25/7 at 11:34

I have already tried by doing this but the results are same. – Ellanellard 25/7 at 11:37

@Shivam - please show us a similar table with datetime2 – Graveclothes 25/7 at 11:38

Hot tags

Godot Unity Godot Help Programming Godot 4.X GUI GDScript 3D 2D Physics CSharp Godot 3.X VR XR Projects C++

Recommended topics

Hot tags