I want to save date in database table using date datatype

Pavithra L

11y1.6k.NET

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.

39 Replies

Know the answer? Post it — somebody with the same question will find it here.

Upendra Pratap Shahi11y ago
Accepted answer
Hello Pavithra L, your welcome dear.

Kindly accept the answer.
Upendra Pratap Shahi11y ago
Thanks for your appreciation Pavithra L.
Pavithra L11y ago
Hai, It's working thanks.
Upendra Pratap Shahi11y ago
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 L11y ago
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_No,Date1,Employee,Reference,Purpose,S_No,Machine,Job_No,Item,Qty,UOM,T1,Prepared_By,Remarks 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 L11y ago
Hai Raja,

Thanks for your help.
Pavithra L11y ago
Hai
Thanks for your reply.
Raja T11y ago
Hi Pavithra L, Thanks for the appreciation
Subhani Shaikh11y ago
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 T11y ago
Hi Pavithra , If you find anything useful please accept my answer
Raja T11y ago
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 L11y ago
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 T11y ago
Please check connection. mising connection close .
Pavithra L11y ago
Hai Raja,

I am using your code it was showing error like connection was not closed connection current state is open.
Raja T11y ago
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 L11y ago
Hai Raja,

Here I can use datareader to retrieve the data. so please tell me how to use datareader .
Raja T11y ago
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 L11y ago
Hai,
Anybody can help this, it's urgent.

Pavithra L11y ago

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 L11y ago
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 L11y ago
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 L11y ago
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 T11y ago
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 J11y ago
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 L11y ago
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 L11y ago
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 T11y ago
Hi Pavithra, Please share your code here and also Please check what datatype in database ?
Pavithra L11y ago
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 J11y ago
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 L11y ago
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 L11y ago
Hi,

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

Try like

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

Manas Mohapatra11y ago

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 L11y ago
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 J11y ago
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 L11y ago
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 L11y ago
Thanks for your reply friend,

In windows application

My code is ,

datetimepicker1.text in save query
Raja T11y ago
Hi Pavithra, Try like this

string date = dateTimePicker1.Text;
DateTime enteredDate = DateTime.Parse(date);
Manas Mohapatra11y ago
Please provide below details:

1. Are you using windows or web application
2. Provide code how you are trying to save in database