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.
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 |