c# - 将 DateTime 插入 sql server 2008 时出现奇怪的错误

Question

我正在向 sql server 插入一个日期时间字段。在我的本地计算机上，它工作得很好。示例:sql server 中的日期时间字段是:“2013-07-31 08:00:00.000”，但是当我从服务器运行应用程序时，它会切换日期和月份并像这样插入它:“2013-07-31 15:15:00.000”。

我的相关页面根据今天的日期从 sql server 加载一些条目。像这样:

public List<act_event> return_event_list(DateTime date) //return all events for spesific date
    {

        List<act_event> event_list = new List<act_event>();

        String date_sql = date.ToString("yyyy-MM-dd");


        using (SqlConnection con = connect("igroup20_test2ConnectionString"))
        {

            using (SqlCommand cmd = create_command(con, "select * from act_events where  '" + date_sql + "'>=(CAST(e_start as DATE)) and '" + date_sql + "'<=(CAST(e_end as DATE))"))
            {
                using (SqlDataReader rdr = cmd.ExecuteReader())
                {
                    while (rdr.Read())
                    {
                        act_event a_event = new act_event();
                        a_event.e_num = Convert.ToInt32(rdr["e_num"]);
                        a_event.name = rdr["e_name"].ToString();
                        a_event.start = Convert.ToDateTime(rdr["e_start"]);
                        a_event.end = Convert.ToDateTime(rdr["e_end"]);
                        a_event.description = rdr["e_description"].ToString();
                        a_event.address = rdr["e_address"].ToString();

                        event_list.Add(a_event);


                    }
                }
            }
        }


        return event_list;


    }

这是我插入日期时间字段的方式:

public void add_event(act_event add_avent)
    {
        using (SqlConnection con = connect("igroup20_test2ConnectionString"))
        {
            using (SqlCommand cmd = create_command(con, "insert into act_events values(@e_name, @e_start, @e_end, @e_description, @e_address)"))
            {


                cmd.Parameters.AddWithValue("@e_name", add_avent.name);

                SqlParameter param2 = new SqlParameter("@e_start", SqlDbType.DateTime);
                param2.Value = add_avent.start;
                cmd.Parameters.Add(param2);

                SqlParameter param3 = new SqlParameter("@e_end", SqlDbType.DateTime);
                param3.Value = add_avent.end;
                cmd.Parameters.Add(param3);

                //cmd.Parameters.Add(new SqlParameter("@e_start", SqlDbType.DateTime));
                //cmd.Parameters["@e_start"].Value = DateTime.Parse(add_avent.start.ToString());



                //cmd.Parameters.Add(new SqlParameter("@e_end", SqlDbType.DateTime));
                //cmd.Parameters["@e_end"].Value = DateTime.Parse(add_avent.end.ToString());

                cmd.Parameters.AddWithValue("@e_description", add_avent.description);
                cmd.Parameters.AddWithValue("@e_address", add_avent.address);

                cmd.ExecuteNonQuery();
            }

            //using (SqlCommand cmd2=create_command
        }


    }

我尝试更改选择命令，添加以下内容:

using (SqlCommand cmd = create_command(con, "select * from act_events where ( '" + date_sql + "'>=(CAST(e_start as DATE)) and '" + date_sql + "'<=(CAST(e_end as DATE)) ) or ( '" + date_sql2 + "'>=(CAST(e_start as DATE)) and '" + date_sql2 + "'<=(CAST(e_end as DATE)) ) "))

但对于某些日期，它会给我一个错误:

Conversion failed when converting date and/or time from character string.

我该怎么办？

编辑:

我尝试像这样从 sql studio 本身运行查询:

select * from act_events where ( '2013-08-03'>=(CAST(e_start as DATE)) and '2013-08-03'<=(CAST(e_end as DATE)) ) or ( '2013-03-08'>=(CAST(e_start as DATE)) and '2013-03-08'<=(CAST(e_end as DATE)) )

它给了我:

但是如果我这样运行它:(不同的日期)

select * from act_events where ( '2013-07-30'>=(CAST(e_start as DATE)) and '2013-07-30'<=(CAST(e_end as DATE)) ) or ( '2013-30-07'>=(CAST(e_start as DATE)) and '2013-30-07'<=(CAST(e_end as DATE)) ) 

它给我这个错误:

编辑 2:

在 James 的建议下，我进行了如下参数化查询:

String date_sql = date.ToString("yyyy-MM-dd");
        String date_sql2 = date.ToString("yyyy-dd-MM");

        using (SqlConnection con = connect("igroup20_test2ConnectionString"))
        {

                using (SqlCommand cmd = create_command(con, "select * from act_events where @date1>=(CAST(e_start as DATE)) and @date2<=(CAST(e_end as DATE))"))
            {
                cmd.Parameters.AddWithValue("@date1", date_sql);
                cmd.Parameters.AddWithValue("@date2", date_sql);
                cmd.Parameters.AddWithValue("@date3", date_sql2);
                cmd.Parameters.AddWithValue("@date4", date_sql2);

仍然无法从 sql server 加载正确的条目

然后我尝试了这个查询:

String date_sql = date.ToString("yyyy-MM-dd");
        String date_sql2 = date.ToString("yyyy-dd-MM");

        using (SqlConnection con = connect("igroup20_test2ConnectionString"))
        {

            using (SqlCommand cmd = create_command(con, "select * from act_events where ( @date1>=(CAST(e_start as DATE)) and @date2<=(CAST(e_end as DATE)) ) or ( @date3>=(CAST(e_start as DATE)) and @date4<=(CAST(e_end as DATE)) )"))
            {
                cmd.Parameters.AddWithValue("@date1", date_sql);
                cmd.Parameters.AddWithValue("@date2", date_sql);
                cmd.Parameters.AddWithValue("@date3", date_sql2);
                cmd.Parameters.AddWithValue("@date4", date_sql2);

它再次给我:

Conversion failed when converting date and/or time from character string.

Answer 1

尝试从客户端将日期/时间作为 string 传递给 SQL 从来都不是一个好主意，除非您完全确定服务器区域设置。

切换到参数化查询应该可以解决您的问题。