Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL - Insert NULL into DateTime

Tags:

c#

sql

sql-server

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?

like image 589
Man1 Avatar asked Mar 19 '11 13:03

Man1


1 Answers

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;
}
like image 75
TcKs Avatar answered Oct 05 '22 05:10

TcKs