我们如何更改SQL Server（2008）数据库的日期格式？

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?

对于通过win表单保存的数据,输入日期格式为(dd / mm / yyyy)。数据库显示(yyyy-mm-dd)格式。如何更改日期格式?

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)

日期时间piker(dtTmPkr)格式(dd / mm / yyyy)

3 个解决方案

#1

SQL Server doesn't store a DateTime in any string format - it's stored as an 8 byte numerical value.

SQL Server不以任何字符串格式存储DateTime - 它存储为8字节数值。

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.

各种设置(语言,日期格式)仅影响在SQL Server Management Studio中向您显示DateTime的方式 - 或者在您尝试将字符串转换为DateTime时如何解析它。

There are many formats supported by SQL Server - see the MSDN Books Online on CAST and CONVERT.

SQL Server支持许多格式 - 请参阅CDN和CONVERT上的MSDN联机丛书。

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

实际上,它不是要改变SQL Server中的日期格式 - 它是关于如何格式化和显示从SQL Server检索的日期时间。使用适当的T-SQL CONVERT参数,或在C#前端代码中格式化DateTime

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.

更新:如果您使用问题中显示的查询将DateTimePicker中的值插入SQL Server,您应该没问题 - 它正在插入DateTime参数,因此您不会对日期的字符串格式设置有任何问题。

When you need to convert a DATETIME in SQL Server to a specific format - use

当您需要将SQL Server中的DATETIME转换为特定格式时 - 使用

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.

这将给你一个dd / mm / yyyy(英国/法国)格式的日期。如果您需要不同的格式 - 使用不同的样式(其他数字不是103)。这些样式在这里有很好的记录。

If you read back the DateTime column into C# and you need to format it, then use

如果您将DateTime列读回到C#中,并且需要对其进行格式化,请使用

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.

获得你需要的价值。注意:要格式化月份,请使用MM(大写!)因为mm会格式化DateTime的分钟。

#2

try to parse the Date to datetime object in c# prior to adding in Parameter.

尝试在添加参数之前解析c#中的Date to datetime对象。

DateTime Dt = DateTime.ParseExact(dtTmPkr.Value, "dd/MM/yyyy", CultureInfo.InvariantCulture);
cmd.Parameters.AddWithValue("@currDate", Dt);

#3

-1

Use this :

用这个 :

SqlCommand cmd = new SqlCommand("INSERT INTO WtrTblAllot (WtrNm,Type,No,currDate) values (@WtrNm,@Type,@No,CONVERT(CHAR(10), @currDate, 103))", con);

#1