CODEWITHSUNDEEP
c#sql-serverdatetime
4b0
4b0

Reputation: 22323

DateTime.MinValue and SqlDateTime overflow

I don't want to validate txtBirthDate so I want to pass DateTime.MinValue in database.

My code:

 if (txtBirthDate.Text == string.Empty)
    objinfo.BirthDate = DateTime.MinValue;
 else
     objinfo.BirthDate =  DateTime.Parse(txtBirthDate.Text);

DateTime.MinValue return Date = {1/1/0001 12:00:00 AM}

I got a SQL Error:

SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.

I understand it but I don't understand why DateTime.MinValue returns invalid date time which is unable to insert in database. How to handle this type of situation?

Upvotes: 83

Views: 199659

Answers (10)

David Homer
David Homer

Reputation: 416

If you use DATETIME2 you may find you have to pass the parameter in specifically as DATETIME2, otherwise it may helpfully convert it to DATETIME and have the same issue.

command.Parameters.Add("@FirstRegistration",SqlDbType.DateTime2).Value = installation.FirstRegistration;

Upvotes: 2

Batman
Batman

Reputation: 702

Well... its quite simple to get a SQL min date

DateTime sqlMinDateAsNetDateTime = System.Data.SqlTypes.SqlDateTime.MinValue.Value;

Upvotes: 19

Sankar M
Sankar M

Reputation: 4809

Very simple avoid using DateTime.MinValue use System.Data.SqlTypes.SqlDateTime.MinValue instead.

Upvotes: 140

Abhay Kumar
Abhay Kumar

Reputation: 841

Although it is an old question, another solution is to use datetime2 for the database column. MSDN Link

Upvotes: 12

liorlevi1974
liorlevi1974

Reputation: 77

I am using this function to tryparse

public static bool TryParseSqlDateTime(string someval, DateTimeFormatInfo dateTimeFormats, out DateTime tryDate)
    {
        bool valid = false;
        tryDate = (DateTime)System.Data.SqlTypes.SqlDateTime.MinValue;
        System.Data.SqlTypes.SqlDateTime sdt;
        if (DateTime.TryParse(someval, dateTimeFormats, DateTimeStyles.None, out tryDate))
        {
            try
            {
                sdt = new System.Data.SqlTypes.SqlDateTime(tryDate);
                valid = true;
            }
            catch (System.Data.SqlTypes.SqlTypeException ex)
            {

            }
        }

        return valid;
    }

Upvotes: 2

Jens H
Jens H

Reputation: 4632

Simply put, don't use DateTime.MinVaue as a default value.

There are a couple of different MinValues out there, depending which environment you are in.

I once had a project, where I was implementing a Windows CE project, I was using the Framework's DateTime.MinValue (year 0001), the database MinValue (1753) and a UI control DateTimePicker (i think it was 1970). So there were at least 3 different MinValues that were leading to strange behavior and unexpected results. (And I believe that there was even a fourth (!) version, I just do not recall where it came from.).

Use a nullable database field and change your value into a Nullable<DateTime> instead. Where there is no valid value in your code, there should not be a value in the database as well. :-)

Upvotes: 2

Praveen Nambiar
Praveen Nambiar

Reputation: 4892

Here is what you can do. Though there are lot many ways to achieve it. 

DateTime? d = null;    
if (txtBirthDate.Text == string.Empty)
    objinfo.BirthDate = d;
else
    objinfo.BirthDate =  DateTime.Parse(txtBirthDate.Text);

Note: This will work only if your database datetime column is Allow Null. Else you can define a standard minimum value for DateTime d.

Upvotes: 2

Mediator
Mediator

Reputation: 15378

use extensions

public static class DateTimeExtensions
{
    public static DateTime MinValue(this DateTime sqlDateTime)
    {
        return new DateTime(1900, 01, 01, 00, 00, 00);
    }
}


DateTime date = DateTime.Now;
Console.WriteLine("Minvalue is {0} ", date.MinValue().ToShortDateString());

Upvotes: -7

Dave Bish
Dave Bish

Reputation: 19646

From MSDN:

Date and time data from January 1, 1753, to December 31, 9999, with an accuracy of one three-hundredth second, or 3.33 milliseconds. Values are rounded to increments of .000, .003, or .007 milliseconds. Stored as two 4-byte integers. The first 4 bytes store the number of days before or after the base date, January 1, 1900. The base date is the system's reference date. Values for datetime earlier than January 1, 1753, are not permitted. The other 4 bytes store the time of day represented as the number of milliseconds after midnight. Seconds have a valid range of 0–59.

SQL uses a different system than C# for DateTime values.

You can use your MinValue as a sentinel value - and if it is MinValue - pass null into your object (and store the date as nullable in the DB).

if(date == dateTime.Minvalue)
    objinfo.BirthDate = null;

Upvotes: 2

Jon Skeet
Jon Skeet

Reputation: 1502786

Basically, don't use DateTime.MinValue to represent a missing value. You can't use DateTime.MinValue in a SQL Server DateTime field, as SQL Server has a minimum value of the start of 1753.

Instead, make your BirthDate property a Nullable<DateTime> (aka DateTime?), and set it to null when you don't have a value. Also make sure your database field is nullable. Then you just need to make sure that that null ends up as a NULL value in the database. Exactly how you do that will depend on your data access, which you haven't told us anything about.

Upvotes: 107

Related Questions