.NET DateTime to SqlDateTime Conversion

Question

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.

Hovel

@JohnSaunders: Yeah, that is kinda picky. – Gifu

Answer 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");

Afflict

@Winston: Yes my incoming date can be null sometime. – Hovel

@Winston: You should not format your date as a string for inclusion in a SQL statement. Use parameterised SQL with a strongly-typed parameter instead. – Garrity

I agree with luke. What if you were born on the "users" of the month "table", in the year "drop"? (Seriously tho, do what Luke says) – Paramaribo

Rob: Not possible with a .NET DateTime object, as the formatting is directly converted from numeric values. – Cepeda

@LukeH, what's wrong with doing that? The format looks culture/region-independent, so it might be reliable. (Feel free to let me know if I'm wrong, though.) – Marcos

@Sam: In addition to the security issue mentioned by Rob above, the yyyy-MM-dd format isn't completely culture-independent. More details here: sqlblog.org/2009/10/16/… – Garrity

@LukeH, thanks! I didn't know that. Regarding the security issue pointed out by Rob, is it really possible for .NET to format a MM or dd for a DateTime as users or table? – Marcos

@Sam: var c = (CultureInfo)Thread.CurrentThread.CurrentCulture.Clone(); c.DateTimeFormat.TimeSeparator = "';DROP TABLE users;--"; Thread.CurrentThread.CurrentCulture = c; Console.WriteLine("SELECT x FROM y WHERE z = '" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "'"); – Garrity

@Sam: I don't know whether similar attacks are possible/likely in the OP's real-world situation; I'd rather just use proper parameterised SQL and avoid the risk altogether. – Garrity

Use "yyyy-MM-dd HH:mm:ss.fff" to include milliseconds. – Heyes

Answer 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.

Fitzsimmons

Answer 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.

Mattress

Answer 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.

Sacken

Answer 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"}'");

Feed

Answer 6

var sqlCommand = new SqlCommand("SELECT * FROM mytable WHERE start_time >= @StartTime");
sqlCommand.Parameters.Add("@StartTime", SqlDbType.DateTime);
sqlCommand.Parameters("@StartTime").Value = MyDateObj;

Chang

While this code may solve the question, including an explanation of how and why this solves the problem would really help to improve the quality of your post, and probably result in more up-votes. Remember that you are answering the question for readers in the future, not just the person asking now. Please edit your answer to add explanations and give an indication of what limitations and assumptions apply. From Review – Depersonalization