• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2042
  • Last Modified:

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.
0
dodgerfan
Asked:
dodgerfan
  • 2
  • 2
1 Solution
 
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
 
Guy Hengel [angelIII / a3]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
 
dodgerfanAuthor Commented:
Yep, that got it. Thanks for the help.
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now