Round .NET DateTime milliseconds, so it can fit SQL Server milliseconds

Question

I want to convert the datetime value to the value that I will get from SQL Server 2008.

SQL Server truncate the milliseconds to 3 digits, so I truncate the milliseconds already. But the problem is that as you can see here: Milliseconds wrong when converting from XML to SQL Server datetime. SQL Server also has an precision issue.

score 28 · Answer 1 · answered Feb 17 '12 at 03:25

28

Here's what you want:

using System.Data.SqlTypes; // from System.Data.dll

public static DateTime RoundToSqlDateTime(DateTime date)
{
  return new SqlDateTime(date).Value;
}

answered Feb 17 '12 at 03:25

RobSiklos

8,348
5
47
77

Nicholas Carey · Answer 2 · 2014-10-06T16:34:39.533

A little late to the party, but here's a solution, based on the SQL Server docs for the datetime datatype for different versions of SQL Server:

For any given date/time value, this should give you exactly the same value as SQL Server will:

public static class DateTimeExtensions
{
                                   //  milliseconds modulo 10:    0    1    2    3    4    5    6    7    8    9
    private static readonly int[]    OFFSET                  = {  0 , -1 , +1 ,  0 , -1 , +2 , +1 ,  0 , -1 , +1 } ;
    private static readonly DateTime SQL_SERVER_DATETIME_MIN = new DateTime( 1753 , 01 , 01 , 00 , 00 , 00 , 000 ) ;
    private static readonly DateTime SQL_SERVER_DATETIME_MAX = new DateTime( 9999 , 12 , 31 , 23 , 59 , 59 , 997 ) ;

    public static DateTime RoundToSqlServerDateTime( this DateTime value )
    {
        DateTime dt           = new DateTime( value.Year , value.Month , value.Day , value.Hour , value.Minute , value.Second , value.Millisecond) ;
        int      milliseconds = value.Millisecond ;
        int      t            = milliseconds % 10 ;
        int      offset       = OFFSET[ t ] ;
        DateTime rounded      = dt.AddMilliseconds( offset ) ;

        if ( rounded < SQL_SERVER_DATETIME_MIN ) throw new ArgumentOutOfRangeException("value") ;
        if ( rounded > SQL_SERVER_DATETIME_MAX ) throw new ArgumentOutOfRangeException("value") ;

        return rounded ;
    }
}

It will not, however, work properly, for smalldatetime or the new datetime2 datatypes.

Isn't this the same as 'new SqlDateTime(myDateTime).Value' ? — Simon Mourier, Mar 15 '11 at 22:31
+1 only because the code is helpful for interoperability with other programming languages. ;) — Rami A., Sep 16 '12 at 17:44

Bob · Answer 3 · 2016-12-12T14:32:57.093

9

Recommend building upon @RobSiklos solution since use of SqlDateTime in this fashion results in the loss of timezone information that the 'date' argument provided. Find its best practice to ensure timezone info is consistent at the point of conversion by adding a call to DateTime.SpecifyKind:

using System.Data.SqlTypes; // from System.Data.dll

public static DateTime RoundToSqlDateTime(DateTime date)
{
  return DateTime.SpecifyKind( new SqlDateTime(date).Value, date.Kind);
}

edited Dec 12 '16 at 14:32

answered Dec 08 '16 at 18:54

Bob

91
1
2

Click the link to learn more about the [SqlDateTime](https://learn.microsoft.com/en-us/dotnet/api/system.data.sqltypes.sqldatetime) Struct. – stomy Jun 30 '20 at 20:21

score 3 · Answer 4 · edited Jun 27 '10 at 11:46

This code should work:

        int ticksInMillisecond = 10000;
        DateTime t1 = DateTime.Now;
        DateTime t2 = new DateTime(t1.Ticks / ticksInMillisecond * ticksInMillisecond);

But considering SQL Server's precision issue, I would rather truncate it to two digits after second:

        int precisionTicks = 100000;
        DateTime t1 = DateTime.Now;
        DateTime t2 = new DateTime(t1.Ticks / precisionTicks * precisionTicks);

score 0 · Answer 5 · answered Jun 20 '23 at 18:00

0

Construct a new DateTime object

var d = DateTime.UtcNow;
return new(d.Year, d.Month, d.Day, d.Hour, d.Minute, d.Second, d.Millisecond, d.Kind);

answered Jun 20 '23 at 18:00

Peter L

2,921
1
29
31

Round .NET DateTime milliseconds, so it can fit SQL Server milliseconds

5 Answers5

Linked