A common mistake beginners make when working with SQL is trying to format their output at the database layer, as opposed to simply doing this at the presentation layer (i.e., client application, reporting tool, web page, etc). I've covered that quite a bit in various blog posts, but I've only touched upon another similar issue which I feel is equally as important and also commonly mishandled.
In the SqlTeam forums, I often see code that accepts DateTime input in the form of a string value (say, from a TextBox on a web form) and uploads that value to the database written like this:
SqlCommand c = new SqlCommand();
c.CommandText = "insert into SomeTable (DateCol) values ('" + txtDate.Text + "')";
c.ExecuteNonQuery();
Now, I think that hopefully even most beginners will agree that this is bad code. The primary issue, of course, is SQL Injection. Avoiding SQL Injection is very easy to do using Parameters. So, let's say that you rewrite this code using parameters like this:
SqlCommand c = new SqlCommand();
c.CommandText = "insert into SomeTable (DateCol) values (@DateVal)";
c.Parameters.AddWithValue("@DateVal",txtDate.Text);
c.ExecuteNonQuery();
Looking at that, it seems we have done quite a bit better and should be happy with the code. It works well, and no injection is possible. But there is still an issue! Why? The txtDate.Text property returns a string, not a DateTime! And, since we are not setting the data type of the parameter explicitly, the parameter being passed is a string (i.e., VARCHAR or NVARCHAR) value, not a true DateTime value. This means that SQL Server must implicitly cast your string to a DateTime to store it in your table, and this may or may not work successfully, or as expected, depending on how the string is formatted.
I've said it over and over and I'll say it again: The concept of formatting dates should never be something that your database code should ever worry about. The database layer should be accepting DateTime data from clients, and returning DateTime data to your clients. Where and how the client got the data before passing it to the database, or what the client does with the data in terms of formatting after receiving it from the database is of no concern to the database itself.
So, we might decide that to fix this, we can simply declare the data type of the parameter explicitly:
SqlCommand c = new SqlCommand();
c.CommandText = "insert into SomeTable (DateCol) values (@DateVal)";
c.Parameters.Add("@DateVal", SqlDbType.DateTime).Value = txtDate.Text;
c.ExecuteNonQuery();
It appears that now we are in good shape, right? Actually -- no! There is still an implicit conversion happening, because we are still passing a string value -- the txtDate.Text property -- to the parameter, not a true DateTime!
Let's try one more time. How can we avoid these implicit conversions? The answer that question is always the same: Convert explicitly! Your client application is fully capable of handling the parsing, validation, and conversion of that string to a true DateTime value, so go ahead and do it:
DateTime dateval = DateTime.Parse(txtDate.Text); // plus more code to validate, of course
SqlCommand c = new SqlCommand();
c.CommandText = "insert into SomeTable (DateCol) values (@DateVal)";
c.Parameters.Add("@DateVal", SqlDbType.DateTime).Value = dateval;
c.ExecuteNonQuery();
Now we are in business! Before we even create the SqlCommand object, we have a true DateTime value that we are ready to pass along to SQL Server. Our SQL code doesn't need to worry about formatting, parsing, converting, or anything -- it is being passed a completely valid piece of data with the correct type. In short, we can now be sure that whatever value we came up with for the date in our client code is exactly the value that will be stored in our database. That's the idea, right?
So, please, don't rely on your database code to validate your input. Don't just pass along generic string data and "hope" that at the end of the day the database can "handle it". Eliminate the chance of anything going wrong and write your code to explicitly cast and convert and validate any and all input before the database even comes into the picture.
