.NET DateTime to SqlDateTime Conversion

问题:

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

回答1:

Is it possible that the date could actually be outside that range? Does it come from user input? If the answer to either of these questions is yes, then you should always check - otherwise you're leaving your application prone to error.

You can format your date for inclusion in an SQL statement rather easily:

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

回答2:

If you are checking for DBNULL, converting a SQL Datetime to a .NET DateTime should not be a problem. However, you can run into problems converting a .NET DateTime to a valid SQL DateTime.

SQL Server does not recognize dates prior to 1/1/1753. Thats the year England adopted the Gregorian Calendar. Usually checking for DateTime.MinValue is sufficient, but if you suspect that the data could have years before the 18th century, you need to make another check or use a different data type. (I often wonder what Museums use in their databases)

Checking for max date is not really necessary, SQL Server and .NET DateTime both have a max date of 12/31/9999 It may be a valid business rule but it won't cause a problem.

回答3:

Also please remember resolutions [quantum of time] are different.

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

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

回答4:

on my quest to do this with entitie, i stumbled over here, just hitting back to post what i've found out...

when using EF4, "a sql's" datetime column can be filled from .NET's DateTime using BitConverter.

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

also Fakrudeen's link brought me further... thank you.

回答5:

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

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