Weird bug when inserting DateTime to sql server 2008

Question

I'm inserting a datetime field to sql server. From my local computer it works just fine. example: the datetime field in sql server is : "2013-07-31 08:00:00.000", but when I run the application form the server it switches the day and month and insert it like this: "2013-07-31 15:15:00.000".

My relevant page loads some entries from the sql server depending on today's date. Like this:

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;


    }

This is how I insert the datetime fields:

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
        }


    }

I tried changing the select command, adding this:

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)) ) "))

but for some of the dates it gives me an error:

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

What should I do?

EDIT:

I tried to run the query from the sql studio itself like this:

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)) )

it gives me:

But if I run it like this: (different 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)) ) 

it gives me this error:

EDIT2:

After James suggestion I made a parameterized query like this:

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);

which still won't load the correct entries from the sql server

Then I tried this query:

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);

And again it just give me:

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

Answer 1

The problems you encounter are due to string to date conversions in one point or another, both on the client and the server's side

You don't need to do any conversionf to string or date if you use a parameterized query and you pass DateTime values, provided of course that the type of your table's fields is also a date type.

The following code makes no conversions at all:

DateTime date1=DateTime.Today.AddMonths(-1);
DateTime date2=DateTime.Today.AddMonths(1);

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

    using (SqlCommand cmd = create_command(con, 
       "select * from act_events where " +
        "  (@date1>=e_start  and @date1 <= e_end) " + 
        "  or (@date2>= e_start and @date2 <= e_end) "))
        {
            cmd.Parameters.AddWithValue("@date1", date1);
            cmd.Parameters.AddWithValue("@date2", date2);

You don't need to specify the same date multiple times. If this query doesn't work it's either because the e_start and e_end fields are text fields or the WHERE clause is not what you expected.

You should also check whether @date2 is used correctly. Your query seems to look for records that cover @date1 or don't cover @date2. Is this correct?

Answer 2

It's never ever really a good idea to try pass date/time's to SQL as string from the client unless you are absolutely certain of the server locale.

Switching to a parameterized query should resolve your issue.