在使用的SqlParameter DateTime的存储过程，格式错误 [英] Using DateTime in a SqlParameter for Stored Procedure, format error
问题描述
我试图调用存储过程（在SQL 2005服务器）从C＃，.NET 2.0中使用的DateTime 作为值到的SqlParameter 。在存储过程中的SQL类型是'日期时间'。
I'm trying to call a stored procedure (on a SQL 2005 server) from C#, .NET 2.0 using DateTime as a value to a SqlParameter. The SQL type in the stored procedure is 'datetime'.
执行从SQL Management Studio中存储过程工作正常。但每次我把它从C＃我得到的日期格式错误。
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事件探查器观看的叫声，我然后复制粘贴 EXEC 打电话，看看发生了什么事情。这些都是我的观察和注意事项什么，我已经尝试：
When I run SQL Profiler to watch the calls, I then copy paste the exec call to see what's going on. These are my observations and notes about what I've attempted:
1）如果我通过了的DateTime 直接作为的DateTime 或转换为 SQLDATETIME ，本场是由单引号，比如
1) If I pass the DateTime in directly as a DateTime or converted to SqlDateTime, the field is surrounding by a PAIR of single quotes, such as
@Date_Of_Birth=N''1/8/2009 8:06:17 PM''
2）如果我通过了的DateTime 在一个字符串，我只得到单引号
2) If I pass the DateTime in as a string, I only get the single quotes
3）使用 SqlDateTime.ToSqlString（）甚至转换为通用时间后，不会导致UTC格式的时间字符串（）
3) Using SqlDateTime.ToSqlString() does not result in a UTC formatted datetime string (even after converting to universal time)
4）使用则DateTime.ToString（）不会导致UTC格式的时间字符串。
4) Using DateTime.ToString() does not result in a UTC formatted datetime string.
5）手动设置的DbType 为的SqlParameter 到的DateTime 不改变上述意见。
5) Manually setting the DbType for the SqlParameter to DateTime does not change the above observations.
所以，我的问题的话，是如何在地球上我收到C＃来传递格式正确的时间的SqlParameter ？当然，这是一个常见的情况，为什么会这样很难得到工作吗？我似乎无法的DateTime 转换为字符串，它是SQL compatable（例如2009-01-08T08：22：45'）
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 DateTime to a string that is SQL compatable (e.g. '2009-01-08T08:22:45')
修改
RE：贝丽中，code到实际执行存储过程如下：
RE: BFree, the code to actually execute the sproc is as follows:
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()));
其他修改
一个我认为最有可能的工作：
The one I thought most likely to work:
SqlParameter param = new SqlParameter("@Date_Of_Birth",
System.Data.SqlDbType.DateTime);
param.Value = DOB;
在exec调用这个值作为结果出现在SQL事件探查器
Results in this value in the exec call as seen in the SQL Profiler
@Date_Of_Birth=''2009-01-08 15:08:21:813''
如果我修改此是：
@Date_Of_Birth='2009-01-08T15:08:21'
它的工作原理，但它不会对单引号的解析和它不会转换为的DateTime 正确的日期和时间，并与之间的空间上月底毫秒。
It works, but it won't parse with pair of single quotes, and it wont convert to a DateTime correctly with the space between the date and time and with the milliseconds on the end.
更新和成功
我复制/粘贴code上面从下面的请求后。我整理东西在这里和那里要简洁。原来我的问题是在code我离开了，我敢肯定你的任何人会在瞬间已经发现。我裹在一个事务中我的存储过程的调用。原来，我根本没有做器transaction.commit（） !!!!!我很惭愧地说，但你有它。
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.
我仍然不知道这是怎么回事的语法我从分析器回来。的同事，看着与自己的从他的电脑分析器的实例，它返回正确的语法。看着从我探查了同样的处决显示不正确的语法。它充当红鲱鱼，让我相信有一个查询语法问题，而不是更简单和真正的答案，这是我需要提交事务！
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.
推荐答案
你是如何设置的<一个href=\"http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparameter.aspx\"><$c$c>SqlParameter?你应该设置<一个href=\"http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparameter.sqldbtype.aspx\"><$c$c>SqlDbType物业以<一个href=\"http://msdn.microsoft.com/en-us/library/system.data.sqldbtype.aspx\"><$c$c>SqlDbType.DateTime然后通过 的DateTime 直接到参数（不要转换为字符串，你所要求的一堆问题的话）。
How are you setting up the SqlParameter? You should set the SqlDbType property to SqlDbType.DateTime and then pass the DateTime directly to the parameter (do NOT convert to a string, you are asking for a bunch of problems then).
您应该能够获得的价值到数据库。如果不是，这里是如何做到这一点很简单的例子：
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();
}
}
}
我觉得这里的问题的一部分是，你担心的事实，时间是UTC是不被输送到SQL Server。为此，你不应该，因为SQL Server不知道某个特定时间在一个特定的区域设置/时区。
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（除非你的服务器有相同的时区的客户端code生成的DateTime ，即使在当时，这是一个风险，IMO）。 SQL Server将保存这个值，当你找回来，如果你想在本地时间来显示它，你必须自己（其中的DateTime 结构很容易做呢）。
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 DateTime struct will easily do).
所有这一切是说，如果你执行转换，然后通过转换UTC日期（即通过调用<一个获得的日期href=\"http://msdn.microsoft.com/en-us/library/system.datetime.touniversaltime.aspx\"><$c$c>ToUniversalTime方法，而不是转换为字符串）存储过程。
All that being said, if you perform the conversion and then pass the converted UTC date (the date that is obtained by calling the ToUniversalTime method, not by converting to a string) to the stored procedure.
当你得到的价值回来，叫<一个href=\"http://msdn.microsoft.com/en-us/library/system.datetime.tolocaltime.aspx\"><$c$c>ToLocalTime方法来获取时间在本地时区。
And when you get the value back, call the ToLocalTime method to get the time in the local time zone.
这篇关于在使用的SqlParameter DateTime的存储过程，格式错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
