C# 在存储过程的 SqlParameter 中使用 DateTime，格式错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/425870/
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
Using DateTime in a SqlParameter for Stored Procedure, format error
提问by Matt
I'm trying to call a stored procedure (on a SQL 2005 server) from C#, .NET 2.0 using DateTimeas a value to a SqlParameter. The SQL type in the stored procedure is 'datetime'.
我正在尝试从 C#、.NET 2.0 调用存储过程（在 SQL 2005 服务器上），并将其DateTime用作SqlParameter. 存储过程中的 SQL 类型是“日期时间”。
Executing the sproc from SQL Management Studio works fine. But everytime I call it from C# I get an error about the date format.
从 SQL Management Studio 执行 sproc 工作正常。但是每次我从 C# 调用它时，我都会收到关于日期格式的错误。
When I run SQL Profiler to watch the calls, I then copy paste the execcall to see what's going on. These are my observations and notes about what I've attempted:
当我运行 SQL Profiler 来观察调用时，我然后复制粘贴exec调用以查看发生了什么。这些是我对我尝试过的事情的观察和笔记：
1) If I pass the DateTimein directly as a DateTimeor converted to SqlDateTime, the field is surrounding by a PAIR of single quotes, such as
1) 如果我DateTime直接将in 作为 aDateTime或转换为SqlDateTime，则该字段被一对单引号包围，例如
@Date_Of_Birth=N''1/8/2009 8:06:17 PM''
2) If I pass the DateTimein as a string, I only get the single quotes
2）如果我将传入DateTime作为字符串，我只会得到单引号
3) Using SqlDateTime.ToSqlString()does not result in a UTC formatted datetime string (even after converting to universal time)
3) 使用SqlDateTime.ToSqlString()不会产生 UTC 格式的日期时间字符串（即使在转换为通用时间之后）
4) Using DateTime.ToString()does not result in a UTC formatted datetime string.
4) 使用DateTime.ToString()不会产生 UTC 格式的日期时间字符串。
5) Manually setting the DbTypefor the SqlParameterto DateTimedoes not change the above observations.
5）手动设置DbType为SqlParameter以DateTime不改变上述观察。
So, my questions then, is how on earth do I get C# to pass the properly formatted time in the SqlParameter? Surely this is a common use case, why is it so difficult to get working? I can't seem to convert DateTimeto a string that is SQL compatable (e.g. '2009-01-08T08:22:45')
那么，我的问题是，我到底如何让 C# 在SqlParameter. 这当然是一个常见的用例，为什么工作如此困难？我似乎无法转换DateTime为与 SQL 兼容的字符串（例如“2009-01-08T08:22:45”）
EDIT
编辑
RE: BFree, the code to actually execute the sproc is as follows:
RE：BFree，实际执行sproc的代码如下：
using (SqlCommand sprocCommand = new SqlCommand(sprocName))
{
sprocCommand.Connection = transaction.Connection;
sprocCommand.Transaction = transaction;
sprocCommand.CommandType = System.Data.CommandType.StoredProcedure;
sprocCommand.Parameters.AddRange(parameters.ToArray());
sprocCommand.ExecuteNonQuery();
}
To go into more detail about what I have tried:
要详细了解我的尝试：
parameters.Add(new SqlParameter("@Date_Of_Birth", DOB));
parameters.Add(new SqlParameter("@Date_Of_Birth", DOB.ToUniversalTime()));
parameters.Add(new SqlParameter("@Date_Of_Birth",
DOB.ToUniversalTime().ToString()));
SqlParameter param = new SqlParameter("@Date_Of_Birth",
System.Data.SqlDbType.DateTime);
param.Value = DOB.ToUniversalTime();
parameters.Add(param);
SqlParameter param = new SqlParameter("@Date_Of_Birth",
SqlDbType.DateTime);
param.Value = new SqlDateTime(DOB.ToUniversalTime());
parameters.Add(param);
parameters.Add(new SqlParameter("@Date_Of_Birth",
new SqlDateTime(DOB.ToUniversalTime()).ToSqlString()));
Additional EDIT
附加编辑
The one I thought most likely to work:
我认为最有可能工作的一个：
SqlParameter param = new SqlParameter("@Date_Of_Birth",
System.Data.SqlDbType.DateTime);
param.Value = DOB;
Results in this value in the exec call as seen in the SQL Profiler
在 exec 调用中产生此值，如 SQL Profiler 中所示
@Date_Of_Birth=''2009-01-08 15:08:21:813''
If I modify this to be:
如果我将其修改为：
@Date_Of_Birth='2009-01-08T15:08:21'
It works, but it won't parse with pair of single quotes, and it wont convert to a DateTimecorrectly with the space between the date and time and with the milliseconds on the end.
它可以工作，但它不会用一对单引号解析，并且它不会DateTime正确转换为日期和时间之间的空格以及最后的毫秒数。
Update and Success
更新和成功
I had copy/pasted the code above after the request from below. I trimmed things here and there to be concise. Turns out my problem was in the code I left out, which I'm sure any one of you would have spotted in an instant. I had wrapped my sproc calls inside a transaction. Turns out that I was simply not doing transaction.Commit()!!!!! I'm ashamed to say it, but there you have it.
我在下面的请求后复制/粘贴了上面的代码。为了简洁，我在这里和那里修剪了一些东西。原来我的问题出在我遗漏的代码中，我相信你们中的任何人都会立即发现。我已经将我的 sproc 调用包装在一个事务中。事实证明，我根本没有做transaction.Commit()！！！！！我很惭愧地说出来，但你有它。
I still don't know what's going on with the syntax I get back from the profiler. A coworker watched with his own instance of the profiler from his computer, and it returned proper syntax. Watching the very SAME executions from my profiler showed the incorrect syntax. It acted as a red-herring, making me believe there was a query syntax problem instead of the much more simple and true answer, which was that I need to commit the transaction!
我仍然不知道我从分析器返回的语法发生了什么。一位同事在他的计算机上使用他自己的分析器实例进行了观察，它返回了正确的语法。从我的分析器观看非常相同的执行显示了不正确的语法。它充当了红鲱鱼，让我相信存在查询语法问题，而不是更简单和真实的答案，即我需要提交事务！
I marked an answer below as correct, and threw in some up-votes on others because they did, after all, answer the question, even if they didn't fix my specific (brain lapse) issue.
我将下面的一个答案标记为正确，并对其他答案投了一些赞成票，因为毕竟他们确实回答了这个问题，即使他们没有解决我的具体（大脑失误）问题。
采纳答案by casperOne
How are you setting up the SqlParameter? You should set the SqlDbTypepropertyto SqlDbType.DateTimeand then pass the DateTimedirectly to the parameter (do NOT convert to a string, you are asking for a bunch of problems then).
你是怎么设置的SqlParameter？你应该设定SqlDbType属性来SqlDbType.DateTime，然后通过DateTime直接向参数（不转换为字符串，你所要求的一堆问题，那么）。
You should be able to get the value into the DB. If not, here is a very simple example of how to do it:
您应该能够将值放入数据库中。如果没有，这是一个非常简单的示例，说明如何执行此操作：
static void Main(string[] args)
{
// Create the connection.
using (SqlConnection connection = new SqlConnection(@"Data Source=..."))
{
// Open the connection.
connection.Open();
// Create the command.
using (SqlCommand command = new SqlCommand("xsp_Test", connection))
{
// Set the command type.
command.CommandType = System.Data.CommandType.StoredProcedure;
// Add the parameter.
SqlParameter parameter = command.Parameters.Add("@dt",
System.Data.SqlDbType.DateTime);
// Set the value.
parameter.Value = DateTime.Now;
// Make the call.
command.ExecuteNonQuery();
}
}
}
I think part of the issue here is that you are worried that the fact that the time is in UTC is not being conveyed to SQL Server. To that end, you shouldn't, because SQL Server doesn't know that a particular time is in a particular locale/time zone.
我认为这里的部分问题是您担心时间在 UTC 中的事实没有传达到 SQL Server。为此，您不应该这样做，因为 SQL Server 不知道特定时间位于特定区域设置/时区。
If you want to store the UTC value, then convert it to UTC before passing it to SQL Server (unless your server has the same time zone as the client code generating the DateTime, and even then, that's a risk, IMO). SQL Server will store this value and when you get it back, if you want to display it in local time, you have to do it yourself (which the DateTimestruct will easily do).
如果要存储 UTC 值，请在将其传递给 SQL Server 之前将其转换为 UTC（除非您的服务器与生成DateTime. SQL Server 将存储这个值，当你取回它时，如果你想在本地时间显示它，你必须自己做（DateTime结构很容易做到）。
All that being said, if you perform the conversion and then pass the converted UTC date (the date that is obtained by calling the ToUniversalTimemethod, not by converting to a string) to the stored procedure.
话虽如此，如果您执行转换，然后将转换后的 UTC 日期（通过调用ToUniversalTime方法获得的日期，而不是通过转换为字符串获得的日期）传递给存储过程。
And when you get the value back, call the ToLocalTimemethodto get the time in the local time zone.
并且当您取回值时，调用该ToLocalTime方法以获取本地时区的时间。
回答by BFree
Just use:
只需使用：
param.AddWithValue("@Date_Of_Birth",DOB);
That will take care of all your problems.
这将解决您的所有问题。
回答by K Richard
Here is how I add parameters:
这是我添加参数的方法：
sprocCommand.Parameters.Add(New SqlParameter("@Date_Of_Birth",Data.SqlDbType.DateTime))
sprocCommand.Parameters("@Date_Of_Birth").Value = DOB
I am assuming when you write out DOB there are no quotes.
我假设当你写出 DOB 时没有引号。
Are you using a third-party control to get the date? I have had problems with the way the text value is generated from some of them.
您是否使用第三方控件来获取日期？我在从其中一些生成文本值的方式上遇到了问题。
Lastly, does it work if you type in the .Value attribute of the parameter without referencing DOB?
最后，如果您在不引用 DOB 的情况下键入参数的 .Value 属性，它会起作用吗？
回答by Michael Prewecki
If you use Microsoft.ApplicationBlocks.Datait'll make calling your sprocs a single line
如果你使用Microsoft.ApplicationBlocks.Data它会让你的 sprocs 成为一行
SqlHelper.ExecuteNonQuery(ConnectionString, "SprocName", DOB)
Oh and I think casperOne is correct...if you want to ensure the correct datetime over multiple timezones then simply convert the value to UTC before you send the value to SQL Server
哦，我认为 casperOne 是正确的……如果您想确保多个时区的日期时间正确，那么在将值发送到 SQL Server 之前只需将值转换为 UTC
SqlHelper.ExecuteNonQuery(ConnectionString, "SprocName", DOB.ToUniversalTime())
