SQL Server Stored Procedure update datetime field to null

Posted on 2012-08-24
Last Modified: 2012-08-24
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.
Question by:dodgerfan
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    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
    LVL 59

    Expert Comment

    by:Kevin Cross
    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.


    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,


    Author Comment

    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?
    LVL 142

    Accepted Solution

    this should do:
    SqlParameter pDF = new SqlParameter("@DateFrom", DateFrom.Text );
    if (DateFrom.Text == string.Empty) { pDF.Value = DBNull.Value; }
    SqlParameter pDT = new SqlParameter("@DateTo", DateTo.Text );
    if (DateTo.Text == string.Empty) { pDT.Value = DBNull.Value; }

    Open in new window


    Author Closing Comment

    Yep, that got it. Thanks for the help.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
    ASP.Net to Oracle Connectivity Recently I had to develop an ASP.NET application connecting to an Oracle database.As I am doing it first time ,I had to solve several problems. This article will help to such developers  to develop an ASP.NET client…
    how to add IIS SMTP to handle application/Scanner relays into office 365.
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now