The SQL Server DateTime type is represented by LINQ to SQL as a .Net System.DateTime type. This presents a small problem in that the .Net type supports more precision – a “tick” representing 100 nanoseconds – than the SQL type which is “rounded to increments of .000, .003, or .007 seconds”.

It is worth noting that the actual accuracy of the .Net date is likely to be less than 100 nanoseconds.

This difference in accuracy is not normally a problem but it can be difficult in testing when you might wish to write a value to the database and then read it to check it has been saved correctly: a DateTime will likely not be the same as the value saved as it will have been rounded.

The function below is an attempt to replicate the rounding performed by SQL Server:

/// <summary>
/// Rounds a DateTime as it would be changed by SQL.
/// </summary>
/// <remarks>
/// The .Net DateTime is accurate to 0.1 milliseconds (100 nanoseconds). SQL Server DateTime is "Rounded to increments
/// of .000, .003, or .007 seconds" i.e. 0, 3 or 7 milliseconds.
/// </remarks>
public static DateTime DateTimeAsFromSql(DateTime dateTime)
{
	DateTime rounded = new DateTime(dateTime.Year, dateTime.Month, dateTime.Day, dateTime.Hour, dateTime.Minute, dateTime.Second);
	long ticksDifference = dateTime.Ticks - rounded.Ticks;
	int milliseconds = ((int)(ticksDifference / 100000)) * 10;
	long ticks = ticksDifference - (milliseconds * 10000);
	if (ticks >= 0 && ticks < 17000)
	{
		milliseconds += 0;
	}
	else if (ticks >= 17000 && ticks < 50000)
	{
		milliseconds += 3;
	}
	else if (ticks >= 50000 && ticks < 84000)
	{
		milliseconds += 7;
	}
	else if (ticks >= 84000 && ticks <= 99999)
	{
		milliseconds += 10;
	}
	else
	{
		throw new InvalidOperationException("ticks: " + ticks);
	}

	return rounded.AddMilliseconds(milliseconds);
}

Of course, a better solution is to use one of the other SQL date formats that allow for more precision.