How can I pass DateTime format to MS sql server in a stored procedure?

Question

I hope you could help me in my project. I have been trying to solve this issue long time ago and it doesn't work.

I'm trying to pass a Date time from c# to MS sql server, note that the server stored datetime with format '1900-01-01 00:00:00.000'

it's work perfectly when I use it locally, but when I upload page into a server I get error.

  string connectionString;
            SqlConnection mySqlConnection;
            connectionString = ConfigurationManager.ConnectionStrings[("connectionNamr")].ConnectionString;
           mySqlConnection = new SqlConnection(connectionString);
            SqlCommand mySqlComd = new SqlCommand();
            SqlDataReader reader;
            mySqlComd.CommandText = "ProcedureName";    
            mySqlComd.CommandType = CommandType.StoredProcedure;
            mySqlComd.Connection = mySqlConnection;
            SqlParameter depParam = new SqlParameter("@colName1", SqlDbType.NVarChar, 255);
            SqlParameter empParam = new SqlParameter("@colName2", SqlDbType.NVarChar, 20);
            SqlParameter startDateParam = new SqlParameter("@EVcolName3", SqlDbType.DateTime);
            SqlParameter endDateParam = new SqlParameter("@colName4", SqlDbType.DateTime);
            SqlParameter filterParam = new SqlParameter("@colName5", SqlDbType.NVarChar, 20);
            depParam.Value = string.Empty;
            empParam.Value = employeeID;
            DateTime datet = new DateTime(year,month,day);
            string datet1 = datet.ToString();
            //string datet1 = datet.ToUniversalTime().ToString("yyyy'-'MM'-'dd'T'HH':'mm':'ss'.'fff'Z'");
           // datet1 = datet1.Substring(0, 19);
            startDateParam.Value = Convert.ToDateTime(datet1.Trim()); ;
            endDateParam.Value = Convert.ToDateTime(datet1.Trim()); ;
            filterParam.Value = string.Empty; 
            mySqlComd.Parameters.Add(depParam);
            mySqlComd.Parameters.Add(empParam);
            mySqlComd.Parameters.Add(startDateParam);
            mySqlComd.Parameters.Add(endDateParam);
            mySqlComd.Parameters.Add(filterParam); 
            mySqlConnection.Open();
            mySqlComd.ExecuteNonQuery();
         reader = mySqlComd.ExecuteReader(CommandBehavior.CloseConnection);

            while (reader.Read())
{
 attendance_date = reader["SITE_IN_TIME"].ToString();
}

            mySqlConnection.Close();
            return attendance_date;

        }
        catch (Exception exp)
        {
            return "Error, Exception: " + exp;
        }

When you say that "server stored datetime with format", what do you mean? Is it stored as string? — Szymon
– Szymon, Commented Oct 30, 2013 at 11:28
@Szymon no it stored as DateTime, which means DateTime format — Alaa12
– Alaa12, Commented Oct 30, 2013 at 11:34
1. We will need to know the error you are getting to help diagnose the problem. 2. If the process works locally I would expect a communication error (security / firewall). 3. Is this system going to stay in the same time zone? Use of DateTime passed from the application tier can lead to issues with "timing". I would consider DateTimeOffset in your C# code and UTC based times on your SQL database (of type DateTimeOffset). I generally like to set all timestamps at the database if possible (only have the application pass times that are needed from the user, unless you have a compliance need). — Zack Jannsen
– Zack Jannsen, Commented Oct 30, 2013 at 11:51

Guffa · Accepted Answer · 2013-10-30 11:35:01Z

4

The problem with the date format is because you are converting it to a string and then back to a DateTime value. Depending on the culture settings on the specific server this may or may not work. It may also misinterpret the data, e.g. transforming a date from 2013-10-12 to 2013-12-10.

Just use the DateTime value that you already have:

  DateTime datet = new DateTime(year,month,day);
  startDateParam.Value = datet;
  endDateParam.Value = datet;

Side note:

"note that the server stored datetime with format '1900-01-01 00:00:00.000'"

No, it doesn't. A datetime value is a numeric value representing a point in time, it's not stored as text.

answered Oct 30, 2013 at 11:35

Guffa

703k111 gold badges760 silver badges1k bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Alaa12 Over a year ago

It aslo would be a problem, since it doesn't match server format

Szymon Over a year ago

@Alaa12 There's no server format involved at all. DateTime data type is binary both in .NET code and SQL Server.

Guffa Over a year ago

@Alaa12: When you put the DateTime value into the parameter, it doesn't have any format at all, so there is no format that can mismatch.

Szymon · Accepted Answer · 2013-10-30 11:36:36Z

2

You can just directly pass DateTime to your parameter, there is no need to convert it to string and back to date again:

DateTime datet = new DateTime(year,month,day);
startDateParam.Value = datet;
endDateParam.Value = datet;

answered Oct 30, 2013 at 11:36

Szymon

43k16 gold badges99 silver badges115 bronze badges

Collectives™ on Stack Overflow

How can I pass DateTime format to MS sql server in a stored procedure?

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related