在存储过程的 SqlParameter 中使用 DateTime，格式错误 [英] Using DateTime in a SqlParameter for Stored Procedure, format error

问题描述

我正在尝试使用 DateTime 作为 SqlParameter 的值从 C#、.NET 2.0 调用存储过程(在 SQL 2005 服务器上).存储过程中的 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 执行 sproc 工作正常.但是每次我从 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 Profiler 来观察调用时，我然后复制粘贴 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) 手动将 SqlParameter 的 DbType 设置为 DateTime 不会改变上述观察结果.

5) Manually setting the DbType for the SqlParameter to DateTime does not change the above observations.

那么，我的问题是，我到底如何让 C# 在 SqlParameter 中传递正确格式化的时间?这当然是一个常见的用例，为什么工作如此困难?我似乎无法将 DateTime 转换为与 SQL 兼容的字符串(例如'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: BFree，实际执行sproc的代码如下:

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();
}

要详细了解我的尝试:

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 Profiler 中所见

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.

更新和成功

我在下面的请求后复制/粘贴了上面的代码.我在这里和那里修剪了一些东西以简洁.原来我的问题出在我遗漏的代码中，我相信你们中的任何人都会立即发现.我已经将 sproc 调用封装在一个事务中.事实证明，我根本没有在做 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.

推荐答案

您如何设置 SqlParameter?您应该设置 SqlDbType 属性 到 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 之前将其转换为 UTC(除非您的服务器与生成 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 日期(通过调用 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.

当您取回值时，调用ToLocalTime 方法获取本地时区的时间.

And when you get the value back, call the ToLocalTime method to get the time in the local time zone.

这篇关于在存储过程的 SqlParameter 中使用 DateTime，格式错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！