none
How to convert dateTimePicker.Value to datetime2 of SQL Server?

    Question

  • Hi, 

    I am inserting some values from C# forms to SQL Server Tables. Its datatype is datetime2 in SQL, and I am able to insert without changing/converting any value. But when I am trying to select from that table using C# form by giving a date range from dateTimePicker, I am unable to.

    Can anyone help me with converting the datetimepicker value to datetime2? Or any other solution to this, except changing the SQL datatype.. Thanks in advance.

    Wednesday, August 07, 2013 7:25 AM

Answers

  • If you do not need time on your SQL database, change the datatype to date, but to front end (C#) i guess it doesn't matters; all you can get is date by filtering the time value out.

    While inserting:

    cmd.Parameters.AddWithValue("@DOB", dateTimePicker1.Value.ToString("MM/dd/yyyy"));

    While executing your WHERE query pass the values for @D1 and @D2 similarly (with string formatting .ToString("MM/dd/yyyy")), you should get the results (I'm doing this and I'm getting all the results I want).

    I don't write query on my front end but i write procedure on my database and call them from front end. I don't know but should there be ' (apostrophe) at the beginning and and end of @D1 and @D2? Or i guess it doesn't matters but try if its not working for you.

    Hope this helps.

    Adding breakpoints and checking parameters of objects where I guess to have a problem always gives me answer on debugging my code.


    Thursday, August 08, 2013 8:50 AM

All replies

  • I push them via string.

    dateTimeVarOnFrontEnd.ToString("MM/dd/yyyy");

    works perfectly for me. I found this way compatible with MS SQL Server datetime data type.

    Wednesday, August 07, 2013 8:26 AM
  • How do you select the date from SQL Server? Could you please post your code  and query which performs the actual selection of data based on the values in the DateTimePicker controls?
    Wednesday, August 07, 2013 12:42 PM
  • Lets say you have your datetime from sql on a string.

    or just

    var sqlDate=mySqlTable.Rows[0][[2].ToString();


    to place it into datetimePicker,

    datetimePicker1.Value=Convert.ToDateTime(stringVarWithDateTime);

    To push to db, i already posted on previous reply.

    Hope this helps.

    Thursday, August 08, 2013 1:09 AM
  • Thank You everyone. And sorry for not providing enough info on this problem.

    The code to Insert is : 

    cmd = new SqlCommand("INSERT INTO Customer (Name, DOB) Values(@Name, @DOB)");

    cmd.Parameters.AddWithValue("@Name", textBox1.Text);

    cmd.Parameters.AddWithValue("@DOB", dateTimePicker1.Value);

    And, this code is executing fine, the DOB value in SQL Server is set to datetime2(7). It is being stored in this format "2013-07-25 13:02:19.396420"

    and for selecting people born in specific date range, I am using this:

    cmd = new SqlCommand("Select * from Customer where DOB Between '@D1' And '@D2' ")

    I changed this in multiple ways. Tried converting dateTimePicker.Value to DateTime Object, to String, and into the above format being stored in SQL as well. Nothing seems to be working, and I have no Clue.

    Thursday, August 08, 2013 2:34 AM
  • If you do not need time on your SQL database, change the datatype to date, but to front end (C#) i guess it doesn't matters; all you can get is date by filtering the time value out.

    While inserting:

    cmd.Parameters.AddWithValue("@DOB", dateTimePicker1.Value.ToString("MM/dd/yyyy"));

    While executing your WHERE query pass the values for @D1 and @D2 similarly (with string formatting .ToString("MM/dd/yyyy")), you should get the results (I'm doing this and I'm getting all the results I want).

    I don't write query on my front end but i write procedure on my database and call them from front end. I don't know but should there be ' (apostrophe) at the beginning and and end of @D1 and @D2? Or i guess it doesn't matters but try if its not working for you.

    Hope this helps.

    Adding breakpoints and checking parameters of objects where I guess to have a problem always gives me answer on debugging my code.


    Thursday, August 08, 2013 8:50 AM