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.
Best Answer
These values are precise but not accurate.
If you take the distinct values from
SQL_SysDateTime
and compare them...This returns
15.6219
,15.6205
,15.6215
as differences between them (in ms).As documented here SQL Server uses
GetSystemTimeAsFileTime()
forSYSDATETIME()
/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 promisingSo 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 (includingchrome.exe
andMongoDB
) have requested a low time interval for the Platform Timer Resolution)SQL Server doesn't currently have any native way of calling
GetSystemTimePreciseAsFileTime
and returningdatetime2(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()
withGETUTCDATE()
I do get diffs of3.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.