This problem also appears as - ‘When my program updates Sql Server it gets the date format incorrect.’

This is almost always caused by converting the date to and from a string format at some point during the update process. Using text to pass dates and times is just plain WRONG!

I recently worked with a group who had inherited some code from a software house. In passing a .Net DateTime object to a stored procedure (the problem is exactly the same with inline SQL) the code converted the DateTime object to string format, and then passed it to a stored procedure. The stored procedure expected a datetime parameter. The combination of ADO .Net and Sql Server happily converted the string to a datetime. The code worked fine on the test server and failed on the live server. The rules for converting strings to dates were different between the two servers.

This approach is a poor practice because it relies on something external to the code to be set exactly right for the application to work. Far better is for the code to assemble a .Net DateTime object and pass the object to the stored procedure. The code will then work regardless of the server dateformat setting.

Here’s some sample code. This stored procedure expects a date and passes it back converted to a varchar. It also expects a varchar, and passes it back converted to a datetime. This allows us to see how the conversions work, or do not work.

ALTER PROCEDURE [dbo].[AcceptDate] 
	-- Add the parameters for the stored procedure here
	@InDateAsDateTime DateTime, 
	@OutDateAsVarChar  Varchar(20) output,
	@InDateAsVarChar  Varchar(20),
	@OutDateAsDateTime  DateTime output
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
	set @OutDateAsVarChar = @InDateAsDateTime
	set @OutDateAsDateTime = @InDateAsVarChar
END

This fragment of code calls the stored procedure and displays the two output parameters so we can see what the DateTime and the VarChar values get converted to.

        static void Main(string[] args)
        {
            using (SqlConnection cn = new SqlConnection(
                @"Data Source=.;
            Initial Catalog=Northwind;Integrated Security=True"))
            {
                cn.Open();
                using (var cm = cn.CreateCommand())
                {
                    cm.CommandType = CommandType.Text;
                    cm.CommandText = "set language english";
                    cm.ExecuteNonQuery();
                }
                using (var cm = cn.CreateCommand())
                {

                    DateTime myDateTime = new DateTime(2009, 2, 1);

                    cm.CommandType = CommandType.StoredProcedure;
                    cm.CommandText = "dbo.AcceptDate";
                    cm.Parameters.AddWithValue("@InDateAsDateTime",
                        myDateTime);

                    var outVarChar = cm.Parameters.Add(
                        new SqlParameter("@OutDateAsVarChar",
                            SqlDbType.VarChar, 20));
                    outVarChar.Direction = ParameterDirection.Output;

                    cm.Parameters.AddWithValue("@InDateAsVarChar",
                        myDateTime.ToString("dd-MM-yyyy"));

                    var outDateTime = cm.Parameters.Add(
                        new SqlParameter("@OutDateAsDateTime", 
                            SqlDbType.DateTime));
                    outDateTime.Direction = ParameterDirection.Output;

                    cm.ExecuteNonQuery();

                    Console.WriteLine(outVarChar.Value);
                    Console.WriteLine(outDateTime.Value);
                }
            }
        }

The code always sets the input parameters to 1 Feb 2009. The first parameter, @InDateAsDateTime, is set directly to the DateTime object. The third parameter, @InDateAsVarChar, is set via a character string, and explicitly uses the format dd-MM-yyyy.

Regardless of the setting of language the first parameter gets the value 1 Feb 2009 passed correctly. The value passed to the third parameter depends on the language setting. Under ‘English’ (which means American English) it gets the 2 Jan 2009. Under 'British' it gets the 1 Feb 2009.

This shows that passing DateTime values to Sql Server as strings; gives different results depending on the options in force on the active connection. Passing DateTime values as objects means that the application can successfully pass the correct value regardless of the language or dateformat options in force.

---

Cheers,
- Richard
If this post helped you over a problem, or taught you something new, please login and rate it. Ratings are in the drop down in the top left corner