如何在SQL Server中使用“日期”数据类型

I'm currently trying to create an asp.net web app in c# using Visual Studio. I have a page that acts as a registration page for a parent or a child, depending on which radio button you select. When registering a child, you are required to enter DOB from three separate drop down lists. As it stands, I have the DOB data type in the database set as varchar, meaning the DOB for 05/05/2005 saves in the table as '552005'.

我目前正在尝试使用Visual Studio在c#中创建一个asp.net Web应用程序。我有一个页面作为父母或孩子的注册页面,具体取决于您选择的单选按钮。注册孩子时,您需要从三个单独的下拉列表中输入DOB。按照目前的情况,我将数据库中的DOB数据类型设置为varchar,这意味着05/05/2005的DOB在表中保存为“552005”。

When I set the data type to date or datetime it throws this error:

当我将数据类型设置为date或datetime时,它会抛出此错误:

An exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll but was not handled in user code Additional information: Conversion failed when converting date and/or time from character string.

System.Data.dll中发生类型为“System.Data.SqlClient.SqlException”的异常但未在用户代码中处理附加信息:从字符串转换日期和/或时间时转换失败。

Does this mean I have to parse the string into an int somewhere in the code? If so, how exactly and where in my code would it need to be? I'll include some screenshots along with my code.

这是否意味着我必须在代码中的某个地方将字符串解析为int?如果是这样,我的代码究竟需要在哪里?我将包含一些截图以及我的代码。

Thanks in advance!

提前致谢!

Showing how my form looks and how DOB currently stores in table

显示我的表单的外观以及DOB当前如何存储在表中

protected void submitBtn_Click(object sender, EventArgs e)
{
    SqlConnection connect = new SqlConnection("Data Source=THEBEAST;Initial Catalog=newregDB;Integrated Security=True;Pooling=False");

    if (parentRadBtn.Checked)
    {
        if (firstNameBox.Text == "" || surnameBox.Text == "" || postcodeBox.Text == "" || teleBox.Text == "" || emailBox.Text == "" || userBox.Text == "" || passwordBox.Text == "")
        {
            Response.Write("<script>alert('Please ensure all fields have an entry');</script>");
            successLabel.Text = ("");
            userBox.Text = "";
            firstNameBox.Text = "";
            surnameBox.Text = "";
            postcodeBox.Text = "";
            teleBox.Text = "";
            emailBox.Text = "";
            passwordBox.Text = "";
        }
        else
        {
            SqlCommand pa = new SqlCommand("INSERT INTO parent(parentID, firstname, surname, postcode, telephone, email, password) VALUES (@parentID, @firstname, @surname, @postcode, @telephone, @email, @password)", connect);
            pa.Parameters.AddWithValue("@parentID", userBox.Text);
            pa.Parameters.AddWithValue("@firstname", firstNameBox.Text);
            pa.Parameters.AddWithValue("@surname", surnameBox.Text);
            pa.Parameters.AddWithValue("@postcode", postcodeBox.Text);
            pa.Parameters.AddWithValue("@telephone", teleBox.Text);
            pa.Parameters.AddWithValue("@email", emailBox.Text);
            pa.Parameters.AddWithValue("@password", passwordBox.Text);

            connect.Open();
            pa.ExecuteNonQuery();
            connect.Close();
        }

        if (IsPostBack)
        {
            userBox.Text = "";
            firstNameBox.Text = "";
            surnameBox.Text = "";
            postcodeBox.Text = "";
            teleBox.Text = "";
            emailBox.Text = "";
            passwordBox.Text = "";
        }
    }           
    else if (childRadBtn.Checked)
    {
        if (firstNameBox.Text == "" || dayDobList.Text == "" || monthDobList.Text == "" || yearDobList.Text == "" || genderList.Text == "" || userBox.Text == "" || passwordBox.Text == "")
        {
             Response.Write("<script>alert('Please ensure all fields have an entry');</script>");
             successLabel.Text = ("");
             userBox.Text = "";
             firstNameBox.Text = "";
             dayDobList.Text = "";
             monthDobList.Text = "";
             yearDobList.Text = "";
             genderList.Text = "";
             passwordBox.Text = "";
         }
         else
         {
             SqlCommand ca = new SqlCommand("INSERT INTO children(childID, firstname, dob, gender, password) VALUES (@childID, @firstname, @dob, @gender, @password)", connect);
             ca.Parameters.AddWithValue("@childID", userBox.Text);
             ca.Parameters.AddWithValue("@firstname", firstNameBox.Text);
             ca.Parameters.AddWithValue("@dob", dayDobList.Text +  monthDobList.Text +  yearDobList.Text);
             ca.Parameters.AddWithValue("@gender", genderList.Text);
             ca.Parameters.AddWithValue("@password", passwordBox.Text);

             connect.Open();
             ca.ExecuteNonQuery();
             connect.Close();
         }

         if (IsPostBack)
         {
             userBox.Text = "";
             firstNameBox.Text = "";
             dayDobList.Text = "";
             monthDobList.Text = "";
             yearDobList.Text = "";
             genderList.Text = "";
             passwordBox.Text = "";
         }              
     }
 }

2 个解决方案

#1

Your problem is that you have some data in your table with old format '552005', so when you want to change the column type you get error. So you have two options:

您的问题是您的表中有一些旧格式“552005”的数据,因此当您想要更改列类型时会出现错误。所以你有两个选择:

1- Delete anything from that table, change the column type and start saving dates as datetime or datetime2.

1-从该表中删除任何内容,更改列类型并开始将日期保存为datetime或datetime2。

2- Convert all of the current data to a date string eg. '05/05/2005 00:00:00' and then change the column type to datetime.

2-将所有当前数据转换为日期字符串,例如。 '05 / 05/2005 00:00:00'然后将列类型更改为datetime。

UPDATE:

Also don't forget to add data type to your SqlParameters:

另外不要忘记将数据类型添加到SqlParameters:

SqlParameter dob = new SqlParameter("@sinceDateTime", SqlDbType.DateTime);
dob.Value = new DateTime( Int32.Parse(yearDobList.Text), Int32.Parse(monthDobList.Text), Int32.Parse(dayDobList.Text));
ca.Parameters.Add(dob);

#2

need a date, make a date:

需要约会,约会:

new DateTime(int.Parse(dayDobList.Text), 
             int.Parse(monthDobList.Text), 
             int.Parse(yearDobList.Text))

#1