如何确保系统只将正确的日期格式输入到sql server中

I have a textbox which allows a user to enter a date for their date of birth. The application formats the date to "MM/dd/yyyy". The catch is if someones date of birth is 1/1/2000, l get an error as the date is not valid. How can l correct this so that if the user enters 1/1/2000 into the textbox, the application converts it to 01/01/2000 before insertion into sql server database.

我有一个文本框,允许用户输入他们出生日期的日期。应用程序将日期格式化为“MM / dd / yyyy”。问题是如果某人的出生日期是2000年1月1日,则由于日期无效而得到错误。如何纠正这个问题,以便用户在文本框中输入1/1/2000,应用程序将其转换为01/01/2000,然后再插入到sql server数据库中。

      cn.Open();

      String dateFormat = "MM/dd/yyyy";//The format that the txt_dob control uses
      DateTime parsedDate = DateTime.ParseExact(
      txtdob.Text, dateFormat, CultureInfo.InvariantCulture);

        cmd = new SqlCommand("INSERT INTO register ( DOB) VALUES(@DOB, cn);

        cmd.Parameters.AddWithValue("@DOB", txtdob.Text);
        cmd.ExecuteNonQuery();

      MessageBox.Show("saved")
      cn.Close();

2 个解决方案

#1

Your DateTime.ParseExact is failing because your custom format requires 2 digit month and 2 digit day. Change the custom format to single digit of each and do tryparseexact to only insert correctly formatted dates.

您的DateTime.ParseExact失败,因为您的自定义格式需要2个月的月份和2位数的日期。将自定义格式更改为每个的单个数字,并执行tryparseexact以仅插入格式正确的日期。

cn.Open();

  String dateFormat = "M/d/yyyy";
  DateTime parsedDate;

  if (DateTime.TryParseExact(txtdob, dateFormat, 
      CultureInfo.InvariantCulture, DateTimeStyles.None, out parsedDate))
  {
    cmd = new SqlCommand("INSERT INTO register ( DOB) VALUES(@DOB, cn);

    cmd.Parameters.AddWithValue("@DOB", parsedDate);
    cmd.ExecuteNonQuery();

    MessageBox.Show("saved")
  }

cn.Close();

There are better ways to execute the sql command, but I'm just focusing on solving your question.

有更好的方法来执行sql命令,但我只是专注于解决你的问题。

#2

As others said you should store the date on a datetime column on your database. Then when you insert the date it should be automatically converted to the SQL type you are expecting:

正如其他人所说,您应该将日期存储在数据库的datetime列上。然后,当您插入日期时,它应自动转换为您期望的SQL类型:

cmd.Parameters.Add("@DOB", SqlDbType.Datetime).Value = parsedDate;

#1

cn.Open();

  String dateFormat = "M/d/yyyy";
  DateTime parsedDate;

  if (DateTime.TryParseExact(txtdob, dateFormat, 
      CultureInfo.InvariantCulture, DateTimeStyles.None, out parsedDate))
  {
    cmd = new SqlCommand("INSERT INTO register ( DOB) VALUES(@DOB, cn);

    cmd.Parameters.AddWithValue("@DOB", parsedDate);
    cmd.ExecuteNonQuery();

    MessageBox.Show("saved")
  }

cn.Close();