SQL - Insert NULL into DateTime

问题:

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??

This is my 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;
}

The value that adds to my table column is not NULL, its 1900-01-01.

What do I do?

回答1:

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

回答2:

You can omit optional parameters entirely, instead of setting them to SqlDateTime.Null.

Having said that, the code should still work. How do you read the database? Some viewers display 1900-01-01 for null. What do you see when you run select * from Tables from SQL Server Management Studio?

回答3:

I suspect that there is an implicit conversion from DBNull to 0 for your date because DateTime is not nullable by default. This would reproduce the behaviour you are seeing.

lol, having just seen the Addomar's answer, if the column in SSMS is not actually null but in fact zero and you cant change the database schema (sometimes this isnt possible) then the approach below will work...

You could try something like datacolumn datagridview replace specific value to replace the value zero with some different text or a blank string?

But ideally change the column to be nullable with no default and omit the parameter when it should be null.

回答4:

I tried this in SSMS

declare @InsertDate datetime

set @InsertDate = '   '

select @InsertDate

which returns 1900-01-01 00:00:00.000

so another possibility is your input is whitespace. Try using

if (string.IsNullOrWhiteSpace(InsertDate.Text))

instead of

if (InsertDate.Text == "")