I want to save date in database table using date datatype

Question

Hai friend in my project I want to save date in database table using date datatype. How can I do this please help me? In my code i'm just call the datetimepicker text  but it was showing an error like system.datetime value is in string.

Upendra Pratap Shahi · Answer

Hello Pavithra L  your welcome dear. Kindly accept the answer .

Upendra Pratap Shahi · Answer

Thanks for your appreciation Pavithra L .

Pavithra L · Answer

Hai It's working thanks.

Upendra Pratap Shahi · Answer

string sQry = select yourDate from your_table  ; SqlConnection con = new SqlConnection (constr); SqlCommand cmd = new SqlCommand (sQry con); con.Open(); SqlDataReader dr = cmd.ExecuteReader(); if (dr.Read()) { string valj = dr[ yourDate ].ToString(); dateTimePicker1.Text = valj; } con.Close();

Pavithra L · Answer

Hai  I am using report form in my project .Am I using two datetimepicker. Here I want to filter data's based on the Date . But I have one problem while filter the data from 01.01.2015 this date to 10.10.2015 this date. but it can filter all the data's in the datatable . Here is my code for filtering data's else if (comboBox1.Text == Date Range) { SqlDataAdapter da = new SqlDataAdapter(select V_NoDate1EmployeeReferencePurposeS_NoMachineJob_NoItemQtyUOMT1Prepared_ByRemarks from In_House_Issues where Date1 between ' + dateTimePicker1.Text + ' AND ' + dateTimePicker2.Text + '  con); DataTable dt = new DataTable(); da.Fill(dt); dataGridView1.DataSource = dt; if (centeredContentOnpageToolStripMenuItem.Checked == true) { dataGridView1.Columns[12].Visible = false; dataGridView1.Columns[13].Visible = false; dataGridView1.Columns[14].Visible = false; } else { dataGridView1.Columns[12].Visible = true; dataGridView1.Columns[13].Visible = true; dataGridView1.Columns[14].Visible = true; } }

Pavithra L · Answer

Hai Raja Thanks for your help.

Pavithra L · Answer

Hai Thanks for your reply.

Raja T · Answer

Hi Pavithra L  Thanks for the appreciation

Subhani Shaikh · Answer

Hi Use this method and convert date and store public string Format_Date(string strDate) { string strNewDate = ; Array arrDate ; Array arrDate1 ; string strDateFormat = ; if (strDate.Length > 0 ) { //21/01/2006//0 1 2 arrDate = strDate.Split(' ') ; strNewDate = arrDate.GetValue(0).ToString() ; arrDate1 = strNewDate.Split('/'); if (strDateFormat.Trim() == ddmmyyyy) { strNewDate = arrDate1.GetValue(2) + / + arrDate1.GetValue(1) + / + arrDate1.GetValue(0) +  000:00:00AM ; } else if (strDateFormat.Trim() == mmddyyyy) { strNewDate = arrDate1.GetValue(2) + / + arrDate1.GetValue(0) + / + arrDate1.GetValue(1) +  00:00:00AM ; } else { strNewDate = arrDate1.GetValue(1) + / + arrDate1.GetValue(0) + / + arrDate1.GetValue(2) +  00:00:00AM ; } } return strNewDate ; }

Raja T · Answer

Hi Pavithra  If you find anything useful please accept my answer

Raja T · Answer

try like this string selectQuery = select selectedDate from tbl_Test ; SqlConnection con = new SqlConnection(constr); SqlCommand cmd = new SqlCommand(selectQuery con); con.Open(); SqlDataReader dr = cmd.ExecuteReader(); if (dr.Read()) { string valj = dr[selectedDate].ToString() ; dateTimePicker1.Text = valj ; } con.Close();

Pavithra L · Answer

Hai Raja Thank You for your answer I got correct output using your code. Nice conversation with you. Once again thanks for your help.

Raja T · Answer

Please check connection. mising connection close .

Pavithra L · Answer

Hai Raja I am using your code it was showing error like connection was not closed connection current state is open.

Raja T · Answer

Hi Pavithra Try this string selectQuery = select selectedDate from tbl_Test ; SqlConnection con = new SqlConnection(constr); SqlCommand cmd = new SqlCommand(selectQuery con); con.Open(); SqlDataReader dr = cmd.ExecuteReader(); if (dr.Read()) { string valj = dr[selectedDate].ToString() ; dateTimePicker1.Text = valj ; }

Pavithra L · Answer

Hai Raja Here I can use datareader to retrieve the data. so please tell me how to use datareader .

Raja T · Answer

Hi Pavithra try this it is working for me. Please checkout To Save string seldate = dateTimePicker1.Value.ToString(yyyy-MM-dd); string insertQuery = insert into tbl_Test( selectedDate ) values(' + seldate + '); SqlConnection con = new SqlConnection(constr); SqlCommand cmd = new SqlCommand(insertQuery con); con.Open(); cmd.ExecuteNonQuery(); con.Close(); MessageBox.Show(Record Inserted Successfully); From Retrieve string selectQuery = select selectedDate from tbl_Test ; SqlConnection con = new SqlConnection(constr); con.Open(); DataSet dst = new DataSet(); SqlDataAdapter dap = new SqlDataAdapter(selectQuery con); dap.Fill(dst DataSetName); DataTable dt = new DataTable(); dt = dst.Tables[0]; if (dt.Rows.Count > 0) { if (dt.Rows[0][selectedDate] != null) { string valj = dt.Rows[0][selectedDate].ToString(); dateTimePicker1.Text = valj; } }

Pavithra L · Answer

Hai Anybody can help this it's urgent.

Pavithra L · Answer

Hai dateTimePicker1.Text = dr [ Date1 ] as string ?? String . Empty ; this is my code for retrieve the data from database table . But it is not retrieve the date and only show the current date.

Pavithra L · Answer

Hai Raja It can save different date's but while retrieve the data it was showing only the today's date . In retrieve code it cannot fetch the date from database table showing the current date of today. How to solve this.

Pavithra L · Answer

Hai Raja dateTimePicker1.Text = dr [ created_date ] as string ?? String . Empty ; I tried this the Date was stored in Datatable but it takes only today's date . If am I changed the date means it was not saved and while retrieve the date from datatable it can show only the today's date. How to save and retrieve different date's in datatable.

Pavithra L · Answer

Hai  I tried your code but again and again it was showing this error unable to cast object of type System.DateTime to type System.String and retrieve data from database table show this error connection was not closed and the connection current state is open.

Raja T · Answer

Hi Pavithra To Save the data to DB Try like this con.Open(); SqlCommand com = new SqlCommand(insert into In_House_Issues values(' + textBox1.Text + '' + dateTimePicker1.Value.ToString(yyyy-MM-dd) + '' + textBox2.Text + '' + textBox4.Text + '' + comboBox2.Text + '' + v1 + '' + v2 + '' + v3 + '' + v4 + '' + v5 + '' + v6 + '' + v7 + '' + textBox7.Text + '' + textBox12.Text + '' + textBox13.Text + ') con); com.ExecuteNonQuery(); con.Close(); To Retrieve data from Db Try like this dateTimePicker1.Text = dr [ created_date ] as string ?? String . Empty ;

Prem Anandh Natchatran · Answer

Try this SqlCommand com = new SqlCommand(insert into In_House_Issues values(' + textBox1.Text + '' + dateTimePicker1 .Value.toString() + '' + textBox2.Text + '' + textBox4.Text + '' + comboBox2.Text + '' + v1 + '' + v2 + '' + v3 + '' + v4 + '' + v5 + '' + v6 + '' + v7 + '' + textBox7.Text + '' + textBox12.Text + '' + textBox13.Text + ') con);

Pavithra L · Answer

con.Open(); SqlCommand com = new SqlCommand(insert into In_House_Issues values(' + textBox1.Text + '' + dateTimePicker1 .Value + '' + textBox2.Text + '' + textBox4.Text + '' + comboBox2.Text + '' + v1 + '' + v2 + '' + v3 + '' + v4 + '' + v5 + '' + v6 + '' + v7 + '' + textBox7.Text + '' + textBox12.Text + '' + textBox13.Text + ') con); com.ExecuteNonQuery(); con.Close(); and in datatable my datatype is date and field name is Date1. My database table name is Knitting_Dyeing.mdf it was created with in the project.

Pavithra L · Answer

Hai Prem In my code I used date datatype but it was saved in datatable after retrieve that data it was showing an exception like unable to cast object of type System.Datetime to type System.String

Raja T · Answer

Hi Pavithra Please share your code here and also Please check what datatype in database ?

Pavithra L · Answer

Hai Manas Your code also show this exception while retrieve data from database table . how can i solve this in my code. Is there any way to solve this problem.

Prem Anandh Natchatran · Answer

Hi Just ensure the datatype of required field in Table. 1. If the field is string type you can use the query like sqlquery = INSERT INTO emp VALUES(' + empid.Text + '' + empName.Text + '' + DTPicker1.Value.ToString(yyyy-MM-dd) + '); 2. If the field is date then you can use the query like sqlquery = INSERT INTO emp VALUES(' + empid.Text + '' + empName.Text + '' + DTPicker1.Value + '); Try this

Pavithra L · Answer

Hi Raja I am used your code but when am click on the save button build error was occured like connection was not closed but it was saved in database table after retrieve that data from database table it was throwing an exception like Unable to cast object of type System.DateTime to type System.String this. How can i solve this in my code.

Pavithra L · Answer

Hi But it was showing this error Unable to cast object of type System.DateTime to type System.String

Raja T · Answer

Hi Try like string date = dateTimePicker1.Text; DateTime enteredDate = DateTime.Parse(date); string date1 = dateTimePicker1.Value.ToString(yyyy-MM-dd);

Manas Mohapatra · Answer

Make sure that you select date picker in UI. Please try below code: DateTime iDate; iDate = dateTimePicker1.Value; MessageBox.Show(Selected date is  + iDate);

Pavithra L · Answer

Hai I am using your code in my query but it was showing an error in data retrieve function the connection was not closed like this.

Prem Anandh Natchatran · Answer

Hi Pavithra try to use a simple query like.. sqlquery = INSERT INTO emp VALUES(' + empid.Text + '' + empName.Text + '' + DTPicker1.Value.ToString(yyyy-MM-dd) + '); depending upon database you may need to change the DTPicker string format.

Pavithra L · Answer

Hai  string date = dateTimePicker1.Text; DateTime enteredDate = DateTime.Parse(date); It was showing an error like unable to cast object of type System.DateTime to type System.String How can i over come this error.

Pavithra L · Answer

Thanks for your reply friend In windows application My code is  datetimepicker1.text in save query

Raja T · Answer

Hi Pavithra Try like this string date = dateTimePicker1.Text; DateTime enteredDate = DateTime.Parse(date);

Manas Mohapatra · Answer

Please provide below details: 1. Are you using windows or web application 2. Provide code how you are trying to save in database