SQL - NULL插入日期时间 [英] SQL - Insert NULL into DateTime
问题描述
我有一个表,我想补充日期时间
进入某些列。我使用存储过程将值插入表。在存储过程中我有接受空插入到表变量。我的问题是,当我尝试插入一个空值到我的表列我在列得到1900-01-01。我该怎么做,所以这不是默认值插入只在colulmn空??
I have a table where I add Datetime
into some columns. I use a stored procedure to insert values into the table. In the stored procedure I have variables that accept null for inserting into the table. My problem is when I try to insert a null value into my table column I get 1900-01-01 in the column. What do I do so instead of this default value insert only NULL in the colulmn??
这是我的SP:
CREATE PROCEDURE dbo.Insert
@InserID int,
@InsertDate Datetime = null,
AS
Insert into Tables(InsertID, InsertDate)
Values(@InsertID, @InsertDate)
我这样做是为了将空值:
I do this to assign a null value:
System.Data.SqlTypes.SqlDateTime getDate;
//set DateTime null
getDate = SqlDateTime.Null;
if (InsertDate.Text == "")
{
cmd1.Parameters["@InsertDate"].Value = getDate;
}
else
{
cmd1.Parameters["@InsertDate"].Value = InsertDate.Text;
}
这增加了我的表列中的值不为空,其1900-01 -01。
The value that adds to my table column is not NULL, its 1900-01-01.
我该怎么办?
推荐答案
我用这模式,我有一个空或不兼容的文本格式,没有任何问题。
I'm using this pattern and I have no problems with nulls or incompatible text format.
cmd1.Parameters.Add["@InsertDate"].Value = textBox1.Text.AsDBDateTime();
// ...
public static class DBValueExtensions {
public static object AsDBDateTime(this string s) {
object dateTimeValue;
var str = s;
if ( null != str ) { str = str.Trim(); }
if ( string.IsNullOrEmpty(str) ) {
dateTimeValue = DBNull.Value;
}
else {
dateTimeValue = DateTime.Parse(str);
}
return dateTimeValue;
}
这篇关于SQL - NULL插入日期时间的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!