DateTime时间戳记有句点而不是冒号 [英] DateTime timestamp has period instead of colon

问题描述

我有一个包含两个DateTime列的数据表:date_from和date_to.

I have a Datatable with two DateTime columns: date_from and date_to.

string dateFrom = row["date_from"].ToString();
string dateTo = row["date_to"].ToString();

在客户环境中运行此代码时，它将以以下格式返回日期:

When this code is run in a customer environment, it returns dates in the following format:

"2016-01-01 00.00.00"
"2016-02-01 00.00.00"

然后将该字符串插入SQL:

This string is then inserted into SQL:

SELECT * FROM my_table
WHERE UPPER(status) = 'N'
AND trans_date >= {ts '1900-01-01 00.00.00' }
AND trans_date <= {ts '1900-01-01 00.00.00' }

执行此SQL时，它返回错误从字符串转换日期和/或时间时转换失败."

When this SQL is executed it returns the error "Conversion failed when converting date and/or time from character string."

因此，一个快速解决方案是运行string.Replace()，其中我用冒号替换句点:

So a quick fix for this is to run a string.Replace() where I replace period with colon:

dateFrom = dateFrom.Replace(".", ":");

但是，我的问题是，为什么返回的日期以句点作为时间戳分隔符而不是冒号?

However, my question is rather why the date is returned with periods as the timestamp separator and not colons?

我已经在本地计算机上创建了此测试，但是不幸的是，在这种情况下，它返回了DateTime字符串的正确表示形式:

I have created this test on my local machine, but unfortunately for this scenario it returns a correct representation of the DateTime string:

DataTable table = new DataTable();
table.Columns.Add("date", typeof(DateTime));

DataRow row = table.NewRow();
row["date"] = DateTime.Now;
table.Rows.Add(row);

DateTime date = DateTime.Parse(table.Rows[0]["date"].ToString());

到目前为止，我的猜测是这与客户文化有关，但是我非常感谢有此经验的人的意见，因此我可以首先就为什么会发生这种情况向客户提供反馈.

My guess so far would be that it has something to do with the customers culture, but I would really appreciate input from someone who has experience with this so I can give feedback to the customer about why this happened in the first place.

这可能只是一个巧合，但是值得一提的是，这仅适用于使用Windows 10的用户.使用Windows 7的每个人都不会收到此错误.

It might just be a coincidence, but it is worth mentioning that this only happens for users with Windows 10. Everyone using Windows 7 does not get this error.

推荐答案

正如您所说，这与客户的文化有关.具体是 CultureInfo.DateTimeFormat .

As you says, this is about customer's culture. Specifically CultureInfo.DateTimeFormat.

此属性返回一个 DateTimeFormatInfo 对象，该对象定义了显示日期和时间的符合文化习惯的格式.

This property returns a DateTimeFormatInfo object that defines the culturally appropriate format of displaying dates and times.

此对象具有属性 TimeSeparator ，该属性获取或设置用于分隔时间部分(即小时，分钟和秒)的字符串.

This object have a property TimeSeparator that gets or sets the string that separates the components of time, that is, the hour, minutes, and seconds.

您可以设置此属性以指定类似:的分隔符.

You can setup this property to specify a separator like :.

但是，更好的方法是将特定的区域性传递给 ToString()方法，例如 CultureInfo.InvariantCulture (与区域性无关):

However a better approach is passing a specific culture to ToString() method like CultureInfo.InvariantCulture (culture-independent):

string dateFrom = ((DateTime)row["date_from"]).ToString(CultureInfo.InvariantCulture);
string dateTo = ((DateTime)row["date_to"]).ToString(CultureInfo.InvariantCulture);

或使用您自己的格式:

string dateFrom = ((DateTime)row["date_from"]).ToString("yyyy-MM-dd HH:mm:ss");
string dateTo = ((DateTime)row["date_to"]).ToString("yyyy-MM-dd HH:mm:ss");

这篇关于DateTime时间戳记有句点而不是冒号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！