SQL 将参数中的 NULL 传递给存储过程中的 DateTime 字段
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5182032/
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
Pass a NULL in a parameter to a DateTime field in a stored procedure
提问by Jamie Taylor
I have a stored procedure which updates a database using the parameters I supply but I'm having trouble passing a NULL to the stored procedure
我有一个存储过程，它使用我提供的参数更新数据库，但我无法将 NULL 传递给存储过程
The field I need to make NULL is a DateTime field
我需要使 NULL 的字段是 DateTime 字段
DB.Parameters.AddWithValue("@date", NULL)
This gives me the error
这给了我错误
'NULL' is not declared. 'Null' constant is no longer supported; use 'System.DBNull' instead
未声明“NULL”。不再支持“空”常量；改用“System.DBNull”
So I tried
所以我试过了
DB.Parameters.AddWithValue("@date", DBNull.Value.ToString())
But this produces the value 1900-01-01 00:00:00.000in the column as it's passing a ""to the field
但这会产生1900-01-01 00:00:00.000列中的值，因为它将 a 传递""给字段
I also tried
我也试过
DB.Parameters.AddWithValue("@date", DBNull.Value)
But it produces this error
但它会产生这个错误
Value of type 'System.DBNull' cannot be converted to 'String'.
“System.DBNull”类型的值无法转换为“String”。
Has anybody got any ideas?
有人有任何想法吗？
回答by Robbie Mills
Or you can add your parameter like this, which gives it a null value in the database if your variable is null:
或者，您可以像这样添加参数，如果您的变量为空，则在数据库中为其提供空值：
DB.Parameters.AddWithValue("@date", myDateTime ?? (object)DBNull.Value);
you need to set it as a nullable type as Amit mentioned.
您需要将其设置为 Amit 提到的可为空类型。
More details and background available at http://evonet.com.au/overview-of-c-nullable-types/
更多细节和背景可在http://evonet.com.au/overview-of-c-nullable-types/ 获得
回答by LukeH
回答by Jamie Taylor
Try this
尝试这个
If you are using class and its property and those property values are used as parameter then you can do this
如果您正在使用类及其属性，并且这些属性值用作参数，那么您可以执行此操作
chnage the signature of the property to
将财产的签名更改为
public DateTime? myDateTime = null;// Here ? is used to make the property nullable.
Also if your date column is of type varchar then correct it to Date (Sql2008) / DateTime(2005).
此外，如果您的日期列是 varchar 类型，则将其更正为 Date (Sql2008) / DateTime(2005)。
//change parameter to this
//把参数改成这个
@SomeDate datetime = null (as suggested by chris)
Allow the field to accept null and then pass the value as
允许该字段接受 null 然后将值传递为
DB.Parameters.Add("@date", DBNull.Value)
回答by daniele3004
This is an example. It's work for me
这是一个例子。这对我有用
if(obj.myDate == DateTime.MinValue)
{
aCommand.Parameters.Add("dateParameter", SqlDbType.Date).Value = DBNull.Value;
}
else
{
aCommand.Parameters.Add("dateParameter", SqlDbType.Date).Value = obj.myDate ;
}
