使用 C# 将日期时间格式转换为 SQL 格式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17418258/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
DateTime format to SQL format using C#
提问by Ace Caserya
I am trying to save the current date time format from C# and convert it to an SQL Server date format like so yyyy-MM-dd HH:mm:ssso I can use it for my UPDATEquery.
我正在尝试从 C# 保存当前日期时间格式并将其转换为 SQL Server 日期格式，yyyy-MM-dd HH:mm:ss这样我就可以将其用于UPDATE查询。
This was my first code:
这是我的第一个代码：
DateTime myDateTime = DateTime.Now;
string sqlFormattedDate = myDateTime.Date.ToString("yyyy-MM-dd HH:mm:ss");
The output on date is okay, but time is always "12:00:00" so I changed my code to the following:
日期输出没问题，但时间总是“12:00:00”，所以我将代码更改为以下内容：
string sqlFormattedDate = myDateTime.Date.ToString("yyyy-MM-dd") + " " +
myDateTime.TimeOfDay.ToString("HH:mm:ss");
It gave me this compile error:
它给了我这个编译错误：
FormatException was unhandled
FormatException 未处理
And suggested I need to parse. So I tried doing this to my code as per my research here in StackOverflow:
并建议我需要解析。因此，我尝试根据我在 StackOverflow 中的研究对我的代码执行此操作：
string sqlFormattedDate = myDateTime.Date.ToString("yyyy-MM-dd") + " " +
myDateTime.Parse.TimeOfDay.ToString("HH:mm:ss");
or
或者
string sqlFormattedDate = myDateTime.Date.ToString("yyyy-MM-dd") + " " +
myDateTime.tryParse.TimeOfDay.ToString("HH:mm:ss");
But it's telling me that it's a method which is not valid for given context. I tried searching for solutions to my problem and I have been stuck for two hours now. I'm still a bit new in C#, can you help me please?
但它告诉我这是一种对给定上下文无效的方法。我尝试寻找我的问题的解决方案，但我已经被困了两个小时。我对 C# 还是有点陌生，你能帮我吗？
采纳答案by Debajit Mukhopadhyay
try this below
在下面试试这个
DateTime myDateTime = DateTime.Now;
string sqlFormattedDate = myDateTime.ToString("yyyy-MM-dd HH:mm:ss.fff");
回答by Rajeev Kumar
Your first code will work by doing this
您的第一个代码将通过这样做
DateTime myDateTime = DateTime.Now;
string sqlFormattedDate = myDateTime.ToString("yyyy-MM-dd HH:mm:ss"); //Remove myDateTime.Date part
回答by Dmitry Bychenko
Your problem is in the "Date" property that truncates DateTimeto date only.
You could put the conversion like this:
您的问题出在DateTime仅截断为日期的“日期”属性中。你可以像这样进行转换：
DateTime myDateTime = DateTime.Now;
string sqlFormattedDate = myDateTime.ToString("yyyy-MM-dd HH:mm:ss"); // <- No Date.ToString()!
回答by alzaimar
The correct answer was already given "use parameters". Formatting a date and passing it as a string to SQL-Server can lead to errors as it depends on the settings how the date is interpreted on the server side. In europe, we write '1.12.2012' to indicate december 1st 2012, whereas in other countries this might be treated as january 12th.
正确答案已经给出“使用参数”。格式化日期并将其作为字符串传递给 SQL-Server 可能会导致错误，因为它取决于设置如何在服务器端解释日期。在欧洲，我们写“1.12.2012”来表示 2012 年 12 月 1 日，而在其他国家，这可能被视为 1 月 12 日。
When issuing statements directly in SSMS I use the format yyyymmddwhich seem to be quite general. I did not encounter any problems on the various installations I worked on so far.
当直接在 SSMS 中发布声明时，我使用的格式yyyymmdd似乎很通用。到目前为止，我在进行的各种安装中没有遇到任何问题。
There is another seldom used format, which is a bit weird but works for all versions:
还有另一种很少使用的格式，有点奇怪，但适用于所有版本：
select { d '2013-10-01' }
will return the first of october 2013.
将于 2013 年 10 月的第一天返回。
select { ts '2013-10-01 13:45:01' }
will return october 1st, 1:45:01 PM
将于 10 月 1 日下午 1:45:01 返回
I strongly advice to use parameters and neverformat your own SQL code by pasting together homegrown formatted statement fragments. It is an entry for SQL injection and strange errors (formatting a float value is another potential issue)
我强烈建议使用参数并且永远不要通过将自产的格式化语句片段粘贴在一起来格式化您自己的 SQL 代码。它是 SQL 注入和奇怪错误的条目（格式化浮点值是另一个潜在问题）
回答by Ace Caserya
The Answer i was looking for was:
我正在寻找的答案是：
DateTime myDateTime = DateTime.Now;
string sqlFormattedDate = myDateTime.ToString("yyyy-MM-dd HH:mm:ss");
I've also learned that you can do it this way:
我还了解到你可以这样做：
DateTime myDateTime = DateTime.Now;
string sqlFormattedDate = myDateTime.ToString(myCountryDateFormat);
where myCountryDateFormat can be changed to meet change depending on requirement.
可以根据要求更改 myCountryDateFormat 以满足更改。
Please note that the tagged "This question may already have an answer here:" has not actually answered the question because as you can see it used a ".Date" instead of omitting it. It's quite confusing for new programmers of .NET
请注意，标记为“此问题可能已经在此处有答案：”实际上并未回答该问题，因为正如您所看到的那样，它使用了“.Date”而不是省略了它。对于 .NET 的新程序员来说，这很令人困惑
回答by Steve D
Why not skip the string altogether :
为什么不完全跳过字符串：
SqlDateTime myDateTime = DateTime.Now;
回答by InActive
Using the standard datetime format "s" will also ensure internationalization compatibility (MM/dd versus dd/MM):
使用标准日期时间格式“s”还可以确保国际化兼容性（MM/dd 与 dd/MM）：
myDateTime.ToString("s");
=> 2013-12-31T00:00:00
Complete Options: (code: sample result)
完整选项：（代码：示例结果）
d: 6/15/2008
D: Sunday, June 15, 2008
f: Sunday, June 15, 2008 9:15 PM
F: Sunday, June 15, 2008 9:15:07 PM
g: 6/15/2008 9:15 PM
G: 6/15/2008 9:15:07 PM
m: June 15
o: 2008-06-15T21:15:07.0000000
R: Sun, 15 Jun 2008 21:15:07 GMT
s: 2008-06-15T21:15:07
t: 9:15 PM
T: 9:15:07 PM
u: 2008-06-15 21:15:07Z
U: Monday, June 16, 2008 4:15:07 AM
y: June, 2008
'h:mm:ss.ff t': 9:15:07.00 P
'd MMM yyyy': 15 Jun 2008
'HH:mm:ss.f': 21:15:07.0
'dd MMM HH:mm:ss': 15 Jun 21:15:07
'\Mon\t\h\: M': Month: 6
'HH:mm:ss.ffffzzz': 21:15:07.0000-07:00
Supported in .NET Framework: 4.6, 4.5, 4, 3.5, 3.0, 2.0, 1.1, 1.0
Reference: DateTime.ToStringMethod
.NET Framework 支持：4.6、4.5、4、3.5、3.0、2.0、1.1、1.0
参考：DateTime.ToString方法
回答by Francisco Mier YTerán
only you put "T"+DateTime.Now.ToLongTimeString()+ '2015-02-23'
只有你把 "T"+DateTime.Now.ToLongTimeString()+ '2015-02-23'
回答by Rodrigo Uzcanga
If you wanna update a table with that dateTime, you can use your sql string like this example:
如果你想用那个日期时间更新一个表，你可以像这个例子一样使用你的 sql 字符串：
int fieldId;
DateTime myDateTime = DateTime.Now
string sql = string.Format(@"UPDATE TableName SET DateFieldName='{0}' WHERE FieldID={1}, myDateTime.ToString("yyyy-MM-dd HH:mm:ss"), fieldId.ToString());
回答by nanthakumar
Your problem is in the Dateproperty that truncates DateTimeto date only. You could put the conversion like this:
您的问题出在仅Date截断DateTime日期的属性中。你可以像这样进行转换：
DateTime myDateTime = DateTime.Now;
string sqlFormattedDate = myDateTime.ToString("yyyy-MM-dd HH:mm:ss");
