Adding Null Value To DATE Field In SQL SERVER

Question

I have a table that has a field that is a Date. It allows nulls. I am trying to use a stored procedure to add a value to that field, but when I pass null, I get a

"Stored Procedure expects a value for @ReadyDate."

Here is the stored procedure:

Create PROCEDURE [dbo].[sp_UpdateStatus]
@id int,
@status varchar(20),
@ReadyDate Date
AS
BEGIN
    UPDATE PMF_ToolingRequests
        SET status = @status,
        ReadyDate = @ReadyDate
    WHERE ID = @id
END

Here is the C# code I am using to update the field. It balks when I send an empty string as well:

if (setReadyTime)
{
    cmd.Parameters.Add("@ReadyDate", SqlDbType.Date).Value = DateTime.Today.ToString("yyyy/MM/dd");
}
else
{
    cmd.Parameters.Add("@ReadyDate", SqlDbType.Date).Value = (DateTime?)null;
}

cmd.Parameters.Add("@id", SqlDbType.Int).Value = id;
cmd.Parameters.Add("@status", SqlDbType.VarChar).Value = status;

I also tried:

cmd.Parameters.Add("@ReadyDate", SqlDbType.Date).Value = null;

There is no value in that field at certain times. I would like to fill it in when the condition is met.

There are basically two ways to handle this and both of them have been presented as answers. Either of them will work, it just depends on how you want to handle it. — Sean Lange
– Sean Lange, Commented Aug 7, 2015 at 14:28
if one of these answers worked for you, please consider accepting it — psoshmo
– psoshmo, Commented Aug 7, 2015 at 16:35

psoshmo · Accepted Answer · 2015-08-07 14:29:37Z

6

do

cmd.Parameters.Add("@ReadyDate", SqlDbType.Date).Value = DBNull.Value;

in essence, null in an object oriented language has a slightly different meaning than NULL in your DB, which is why

cmd.Parameters.Add("@ReadyDate", SqlDbType.Date).Value = null;

doesn't work.

Here is an MSDN article talking about DBNull if you want to do some reading.

edited Aug 7, 2015 at 14:29

answered Aug 7, 2015 at 14:19

psoshmo

1,58010 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dgan · Accepted Answer · 2015-08-07 14:20:00Z

2

try this:

Pass ReadyDate with Default value as null

Create PROCEDURE [dbo].[sp_UpdateStatus]
@id int,
@status varchar(20),
@ReadyDate Date=null
AS
BEGIN
    UPDATE PMF_ToolingRequests
        SET status = @status,
        ReadyDate = @ReadyDate
    WHERE ID = @id
END

answered Aug 7, 2015 at 14:20

Dgan

10.3k1 gold badge33 silver badges51 bronze badges

1 Comment

Paolo Over a year ago

that will work but imho is risky because this is the way to provide a default value for parameters and not a method to pass null values. the risk is to have bad calls with null just because a developer forgot to put a parameter in the declaration of a datasource and that mistake went unnoticed.

Collectives™ on Stack Overflow

Adding Null Value To DATE Field In SQL SERVER

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related