How to insert null value into DateTime column?

Question

I am trying to store a null value into a DateTime column. My code is like below:

protected void btnInsert_Click(object sender, EventArgs e)
{
    try
    {
        ChangeModule cm = new ChangeModule();
        cm.CR_REF_NO = txtCRRefNumber.Text.Trim();
        cm.SLA_DELIVERY_DATE_WAIVER = DateTime.ParseExact(txtSLADeliveryDateWaiver.Text, "dd/MM/yy", null);
        else /*This else part is causing problem since I assigned null*/
             cm.SLA_DELIVERY_DATE_WAIVER = null;
    }
    catch (Exception ex)
    {
       throw ex;
    }
}

Code is working fine as long as a proper date exists in SLA_DELIVERY_DATE_WAIVER, but throwing error when there is the blank textbox.

did you try the DateTime? var = null;

MBen
– MBen

2011-09-12 13:47:09 +00:00
Commented Sep 12, 2011 at 13:47 — MBen
– MBen, Commented Sep 12, 2011 at 13:47
try out DateTime.TryParseExact()

sll
– sll

2011-09-12 13:48:45 +00:00
Commented Sep 12, 2011 at 13:48 — sll
– sll, Commented Sep 12, 2011 at 13:48

Jon Skeet · Accepted Answer · 2011-09-12 13:48:21Z

2

You can't set a DateTime property to null, because DateTime is a value type. You'll need to change it to a Nullable<DateTime> (aka DateTime?) property - and then make sure that's handled appropriately in your storage layer. It's unclear what's performing the database interaction at the moment.

You should read up on Nullable Value Types on MSDN.

You'll also need to change your code to actually have an if statement, e.g.

DateTime dt;
if (DateTime.TryParseExact(txtSLADeliveryDateWaiver.Text,
                           "dd/MM/yy", null, DateTimeStyles.None, out dt))
{
    cm.SLA_DELIVERY_DATE_WAIVER = dt;
}
else
{
    cm.SLA_DELIVERY_DATE_WAIVER = null;
}

(You should also get rid of the try/catch block - it's actually harming your code as it'll currently mask the source of exceptions, even aside from cluttering it up.)

answered Sep 12, 2011 at 13:48

Jon Skeet

1.5m893 gold badges9.3k silver badges9.3k bronze badges

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

4 Comments

Ashok kumar Over a year ago

Thank you very much for your help. While declaring the "ChangeModule" class I have declared SLA_DELIVERY_DATE_WAIVER field as Nullable only.

Jon Skeet Over a year ago

@Ashok: It's not clear what you mean, but it needs to be of type Nullable<DateTime>.

Ashok kumar Over a year ago

My problem is that null date is not inserting in the database as I have specified in my post. Showing the following error: Now, as well as before also: Procedure or function 'usp_SaveECR' expects parameter '@SLA_DELIVERY_DATE_WAIVER', which was not supplied.

Jon Skeet Over a year ago

@Ashok: Well that's going to be part of the code you haven't shown. You've only shown the UI code - not the code which is talking to the database.

SquidScareMe · Accepted Answer · 2011-09-12 13:49:17Z

0

If you make SLA_DELIVERY_DATE_WAIVER a nullable DateTime then you can do this:

 protected void btnInsert_Click(object sender, EventArgs e)
    {
        try
        {
            ChangeModule cm = new ChangeModule();
            cm.CR_REF_NO = txtCRRefNumber.Text.Trim();
            cm.SLA_DELIVERY_DATE_WAIVER = String.IsNullOrEmpty(txtSLADeliveryDateWaiver.Text)? null : DateTime.ParseExact(txtSLADeliveryDateWaiver.Text, "dd/MM/yy", null);

        }
        catch (Exception ex)
        {
           throw ex;
        }
    }

answered Sep 12, 2011 at 13:49

SquidScareMe

3,2362 gold badges28 silver badges37 bronze badges

2 Comments

Ashok kumar Over a year ago

Thank you for your reply. I got this following error with the solution you have provided: Type of conditional expression cannot be determined because there is no implicit conversion between '<null>' and 'System.DateTime'

SquidScareMe Over a year ago

Sorry, I don't think I made myself clear enough. My code example requires SLA_DELIVERY_DATE_WAIVER to be made nullable. It looks like a lot of people are saying the same thing. Basically, in the ChangeModule class you have to change the line that says DateTime SLA_DELIVERY_DATE_WAIVER to DateTime? SLA_DELIVERY_DATE_WAIVER. This can have big repercussions in your app, though, so do this carefully and with lots of testing. Good luck!

Waqas · Accepted Answer · 2011-09-12 13:49:27Z

0

DateTime can't have null value but, adding question mark '?' in front or DateTime data type while declaring makes it nullable, something like this:

DateTime? nullableDateTime = null;

answered Sep 12, 2011 at 13:49

Waqas

6,7903 gold badges36 silver badges53 bronze badges

Comments

user604613 · Accepted Answer · 2011-09-12 13:49:43Z

0

DateTime is not Nullable per se, you need to specify it as a Nullable, as already noted.

So

DateTime SLA_DELIVERY_DATE_WAIVER { get; set; }

wont work, you need to do

DateTime? SLA_DELIVERY_DATE_WAIVER { get; set; }

or

Nullable<DateTime> SLA_DELIVERY_DATE_WAIVER { get; set; }

Are you storing this to a database? You need the column to be nullable too then.

answered Sep 12, 2011 at 13:49

user604613

2 Comments

Ashok kumar Over a year ago

Yeah, I have done like this already. My problem is with the stored procedure insert statement.

user604613 Over a year ago

@Ashok Wouldn't harm if you would show that proc and the exact exception text in your post.

sll · Accepted Answer · 2011-09-12 13:55:13Z

0

You got exception when there is the blank textbox because DateTime.ParseExact() thrown an FormatException when string to parse is an empty string. Use DateTime.TryParseExact() instead.

From your question it looks like cm.SLA_DELIVERY_DATE_WAIVER is DateTime, change its type to DateTime? so in case of empty textbox: - TryParseExact() will not fail - cm.SLA_DELIVERY_DATE_WAIVER will be able to store NULL value

edited Sep 12, 2011 at 13:55

answered Sep 12, 2011 at 13:50

sll

62.7k22 gold badges109 silver badges157 bronze badges

Comments

Ashok kumar · Accepted Answer · 2011-09-19 06:59:39Z

0

It is a problem to send null values into date-parameters inside the sql server stored procedires. Hence, whenever there is a need to pass null date from front-end application, pass some min. date like '1/1/1753' for example.

Inside SQL Server stored procedure, check for the null date like below:

IF DATEDIFF(DD, @DATE, '1/1/1753') = 0
    SET @DATE = NULL

Now, you can use @DATE inside SQL Server stored procedure however you like.

answered Sep 19, 2011 at 6:59

Ashok kumar

1,6205 gold badges36 silver badges74 bronze badges

Comments

Diomedes · Accepted Answer · 2013-11-26 19:01:38Z

Modifying the stored procedure works, but I think its a bit sloppy.

You can handle it in code, this work for me:

            DateTime? myDate;

        if (TextBoxWithDate.Text != "")
        {
            myDate = DateTime.Parse(TextBoxWithDate.Text);
        }
        else
        {
            myDate = null;
        }

Make myDate DateTime type but nullable, if the value from the text box is null, make myDate null and send it to the stored procedure.

krivtom · Accepted Answer · 2015-02-16 07:30:58Z

0

if (dtProcessStart.Text.Trim() == "")
{
    cmd.Parameters.Add("@ProcessStartDttm", SqlDbType.DateTime).Value = DBNull.Value;
}

edited Feb 16, 2015 at 7:30

krivtom

25k10 gold badges53 silver badges54 bronze badges

answered Feb 16, 2015 at 7:12

Radhakrishnan V

214 bronze badges

1 Comment

Radhakrishnan V Over a year ago

try this, while passing parameter to store procedure.. DBNULL.Value

Tunaki · Accepted Answer · 2016-05-14 11:54:19Z

0

try
{
    ChangeModule cm = new ChangeModule();
    cm.CR_REF_NO = txtCRRefNumber.Text.Trim();
    cm.SLA_DELIVERY_DATE_WAIVER = DateTime.ParseExact(txtSLADeliveryDateWaiver.Text, "dd/MM/yy", null);
    else
         cm.SLA_DELIVERY_DATE_WAIVER = DBNULL.Value;
}
catch (Exception ex)
{
   throw ex;
}

edited May 14, 2016 at 11:54

Tunaki

138k46 gold badges369 silver badges443 bronze badges

answered May 14, 2016 at 11:43

Radhakrishnan V

214 bronze badges

Collectives™ on Stack Overflow

How to insert null value into DateTime column?

9 Answers 9

4 Comments

2 Comments

Comments

2 Comments

Comments

Comments

Comments

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

9 Answers 9

4 Comments

2 Comments

Comments

2 Comments

Comments

Comments

Comments

1 Comment

Comments

Related