Month and Date stored the wrong way around in ASP to SQL Server

Question

I am creating a C# ASP page that takes dates set by the users in a text box (the date is cheosen using a calendar object and then sent to the text box) and saves them to an SQL server. When the dates are entered, it stores them in MM-DD-YYYY instead of DD-MM-YYYY. This obviously generates errors! Can anyone help me to get the dates to store them in the format I want it in?

protected void Page_Load(object sender, EventArgs e)
{

    if (!Page.IsPostBack)
    {
        DateTime today = DateTime.Now;
        DateTime sevenDays = today.AddDays(7);
        btnDelete.Enabled = false;
        txtStart.Text = today.ToShortDateString();
        txtEnd.Text = sevenDays.ToShortDateString();
    }
    updateGrid();
}
protected void btnSave_Click(object sender, EventArgs e)
{
    if (sqlCon.State == System.Data.ConnectionState.Closed)
        sqlCon.Open();
    SqlCommand sqlCmd = new SqlCommand("MessageUpdateOrCreate", sqlCon);
    sqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
    sqlCmd.Parameters.AddWithValue("@MessageID", (hfMessageID.Value == "" ? 0 : Convert.ToInt32(hfMessageID.Value)));
    sqlCmd.Parameters.AddWithValue("@Sender", txtSender.Text.Trim());
    sqlCmd.Parameters.AddWithValue("@Receiver", txtReceiver.Text.Trim());
    sqlCmd.Parameters.AddWithValue("@Message", txtMessage.Text.Trim());
    sqlCmd.Parameters.AddWithValue("@Yeargroup", ddYear.Text.Trim());
    sqlCmd.Parameters.AddWithValue("@Title", txtTitle.Text.Trim());
    sqlCmd.Parameters.AddWithValue("@Date", txtStart.Text.Trim());
    sqlCmd.Parameters.AddWithValue("@Expiry", txtEnd.Text.Trim());

    sqlCmd.ExecuteNonQuery();
    sqlCon.Close();
    string messageID = hfMessageID.Value;
    clear();

    if (messageID == "")
        lblSuccess.Text = "New message Success";
    else
        lblSuccess.Text = "Updated Success";
    updateGrid();
}

If your field's type are date, then you don't store them on MM-DD-YYYY or DD-MM-YYYY format, you store them on an internal binary (numerical) format. The MM-DD-YYYY or DD-MM-YYYY formats are applied by your application (your data components configuration) when you present that data. — Marc Guillot, Jun 05 '17 at 12:00
You should check out [Can we stop using AddWithValue() already?](http://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/) and stop using `.AddWithValue()` - it can lead to unexpected and surprising results... — marc_s, Jun 05 '17 at 12:22

score 2 · Answer 1 · edited Jun 05 '17 at 12:09

Your problem is these two lines:

sqlCmd.Parameters.AddWithValue("@Date", txtStart.Text.Trim());
sqlCmd.Parameters.AddWithValue("@Expiry", txtEnd.Text.Trim());

You are passing strings to a stored procedure that almost certainly expects a date. Which means the string will be converted to a date in a way you don't control.

So you need to take control. You need to parse the date in the way you need it to be (e.g. dd/mm/yyyy or mm/dd/yyyy). These links will help you with that:

Once you have parsed the string as a date:

Check that the date result is what you expected it to be
Pass that date (rather than the existing string) to the stored procedure

And use explicit parameter types instead of `AddWithValue`. – Dan Guzman Jun 05 '17 at 12:22 — Dan Guzman, Jun 05 '17 at 12:22

Month and Date stored the wrong way around in ASP to SQL Server

1 Answers1