In a previous post I pointed out how .NET's DateTime and SQL Server's DateTime have different percisions and comparing two instances of what was originally the same value could result in a mismatch. I also provided a solution that uses a BIGINT field in SQL Server to store the value as ticks and a user defined function to convert them to SQL's DateTime and back.
The problem with that solution is the extra processing that is required for performing the simple task of saving and loading a date and time value. This becomes an issue when dealing with a large amount of data, such as in the case of rendering of a historical report for example. There is an alternative that eleviates the need for any extra processing when saving and loading the value, but its a compromise.
The difference in percission is minor (only at the millisecond level). Looking at the situation pragmatically, in most scenarios it doesn't really matter. We rarely deal with time down to the level of milliseconds, typically seconds are more than enough. If the milliseconds are ommited both in .NET and in SQL Server, then there is no discrepancies between the values. This means that there is some data loss as milliseconds are removed from the date and time, but if they were not needed in the first place then this is not necessarily a problem.
The following method can be used to strip the milliseconds off a .NET DateTime
public static DateTime PrepareForSql(DateTime dateTime)
{
return new DateTime((long)
Math.Floor((decimal)dateTime.Ticks / 10000000) * 10000000);
}
Update: To those that know me it won't be a surprise, but I'm dumb. I thought I was being clever by using .Ticks and Math.Floor, when just doing the following has exactly the same effect and it is more performant
public static DateTime PrepareForSql(DateTime dateTime)
{
return new DateTime(dateTime.Year, dateTime.Month, dateTime.Day,
dateTime.Hour, dateTime.Minute, dateTime.Second);
}
Run #10
TestNewDate 00:00:00.1702448
TestNewDateUsingTicks 00:00:00.2002880