For data saved via win form, the input date format is(dd/mm/yyyy). The database showsin (yyyy-mm-dd) format. How can I change date format?
private void btnAllocate_Click(object sender, EventArgs e)
{
for (int i = 0; i < dgvDetails.Rows.Count; i++)
{
SqlConnection con = new SqlConnection("server=HP-HP; database=Restaurent; user id=sa; password=sql2008");
SqlCommand cmd = new SqlCommand("INSERT INTO WtrTblAllot (WtrNm,Type,No,currDate) values (@WtrNm,@Type,@No,@currDate)", con);
cmd.Parameters.AddWithValue("@WtrNm", dgvDetails.Rows[i].Cells[0].Value);
cmd.Parameters.AddWithValue("@Type", dgvDetails.Rows[i].Cells[1].Value);
cmd.Parameters.AddWithValue("@No", dgvDetails.Rows[i].Cells[2].Value);
cmd.Parameters.AddWithValue("@currDate", dtTmPkr.Value);
con.Open();
cmd.ExecuteNonQuery();
cmd = null;
}
MessageBox.Show("Added successfully!");
}
date time piker (dtTmPkr) format (dd/mm/yyyy)
SQL Server doesn't store a DateTime in any string format - it's stored as an 8 byte numerical value.
The various settings (language, date format) only influence how the DateTime is shown to you in SQL Server Management Studio - or how it is parsed when you attempt to convert a string to a DateTime.
There are many formats supported by SQL Server - see the MSDN Books Online on CAST and CONVERT.
So really, it's not about changing the date format in SQL Server - it's about how to format and display a Datetime that you retrieve from SQL Server. Use the appropriate T-SQL CONVERT parameters, or format the DateTime in your C# front-end code
Update: if you're inserting the value from the DateTimePicker into SQL Server using your query shown in the question, you should be just fine - it's inserting a DateTime parameter so you won't have any issues with string formatting of your dates.
When you need to convert a DATETIME in SQL Server to a specific format - use
SELECT CONVERT(VARCHAR(50), YourDateColumn, 103)
and this will give you a date in dd/mm/yyyy (British/French) format. If you need a different format - use a different style (some other number than 103). Those styles are very well documented here.
If you read back the DateTime column into C# and you need to format it, then use
string formatted = YourDateTime.ToString("dd/MM/yyyy");
to get the values you need. Watch out: to format the month, use MM (capitalized!) because the mm would format the minutes of your DateTime instead.
이 기사는 인터넷에서 수집됩니다. 재 인쇄 할 때 출처를 알려주십시오.
침해가 발생한 경우 연락 주시기 바랍니다[email protected] 삭제
몇 마디 만하겠습니다