SQL Server Stored Procedure update datetime field to null

I have a stored procedure for editing a record and inserting a record. Two of the fields are datetime fields, a fromdate and a todate. I want to be able to leave them blank, or empty. But whenever I run the edit or insert, SQL server puts in the 1/1/1900 date. Is there a way to get around this and have null in the fields? I'm using C# with ASP.Net to call and run the Stored procedures in SQL Server 2005. Thanks.
dodgerfanAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
this should do:
SqlParameter pDF = new SqlParameter("@DateFrom", DateFrom.Text );
if (DateFrom.Text == string.Empty) { pDF.Value = DBNull.Value; }
cmd.Parameters.Add(pDF);

SqlParameter pDT = new SqlParameter("@DateTo", DateTo.Text );
if (DateTo.Text == string.Empty) { pDT.Value = DBNull.Value; }
cmd.Parameters.Add(pDT);

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
if you put '' (empty string), then indeed sql server puts 1900/1/1 instead.
you need to put NULL instead, in C# code it would be DBNull.Value for the parameter
0
 
Kevin CrossChief Technology OfficerCommented:
EDIT: see post http:Q_27841561.html#a38330539 above. While I was being long-winded, angeliii posted the same explanation and suggestion; therefore, please assign points there. Continue on to my comment and please feel free to post back if you need further clarification. Anyway, sorry for the duplication. Please let us know if you have any difficulties implementing suggestion.

-----
Hi.

First, ensure that the database field accepts NULL and does not have a default value of 1900-01-01, which is date 0. You would get this if, for example, you are trying to convert an empty string to DATETIME.

Second, from C# ensure that you are using parameters with the SqlCommand object. Pass to the parameter DBNull.Value for the date field you want to be NULL. Alternatively, construct your INSERT statement to only include the columns you have values for. If you leave off the date column and NULLs are allowed (without default values), you will get NULL for the dates.

I hope that helps.

Best regards,

Kevin
0
 
dodgerfanAuthor Commented:
Thanks for the help. I'm not sure how to correct it in C#. Right now it looks like:
cmd.Parameters.AddWithValue("@DateFrom", DateFrom.Text);
cmd.Parameters.AddWithValue("@DateTo", DateTo.Text);

How do I correct that to take a null?
0
 
dodgerfanAuthor Commented:
Yep, that got it. Thanks for the help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.