Convert .NET DateTime to SqlDateTime

Question

When converting a .NET DateTime (when the default is (DateTime)) to SqlDateTime, I should always check if the .NET date exists between SqlDateTime.MinValue and SqlDateTime.MaxValue [or] Is there a good way to do this.

Answer 1

Is it possible that the date may be outside this range? Does this come from user input? If the answer to any of these questions is yes, then you should always check - otherwise you leave your application prone to error.

You can easily format the date to include in the SQL statement:

var sqlFormattedDate = myDateTime.Date.ToString("yyyy-MM-dd HH:mm:ss"); 

Answer 2

If you check DBNULL, converting SQL Datetime to .NET DateTime should not be a problem. However, you may encounter problems converting a .NET DateTime to a valid SQL DateTime.

SQL Server does not recognize dates until 1/1/1753. It was this year that England adopted the Gregorian calendar. Usually checking for DateTime.MinValue is enough, but if you suspect that the data may be years before the 18th century, you need to do another check or use a different data type. (I often wonder which museums use in their databases)

Checking the maximum date is not really needed, SQL Server and .NET DateTime have a maximum date of 12/31/9999. This may be a valid business rule, but it will not cause a problem.

Answer 3

Also remember that resolutions [time slice] are different.

http://msdn.microsoft.com/en-us/library/system.data.sqltypes.sqldatetime.aspx

SQL one is 3.33 ms and .net is 100 ns.

Answer 4

in my quest to do it with entitie, I stumbled upon this by simply clicking on the message that I found out ...

when using EF4, the datetime sql column can be populated from a .NET DateTime using BitConverter.

 EntitieObj.thetime = BitConverter.GetBytes(DateTime.Now.ToBinary()); 

also the Fakrudeen link led me further ... thanks.

Answer 5

- To compare only part of the date, you can do:

 var result = db.query($"SELECT * FROM table WHERE date >= '{fromDate.ToString("yyyy-MM-dd")}' and date <= '{toDate.ToString("yyyy-MM-dd"}'");