DateTime.MinValue and SqlDateTime overflow
C#Sql ServerDatetimeC# Problem Overview
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 under stand it but I don't understand why DateTime.MinValue
return invalid date time which is unable to insert in database.How to handle this type of situation?
C# Solutions
Solution 1 - C#
Very simple avoid using DateTime.MinValue
use System.Data.SqlTypes.SqlDateTime.MinValue
instead.
Solution 2 - C#
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.
Solution 3 - C#
Although it is an old question, another solution is to use datetime2 for the database column. MSDN Link
Solution 4 - C#
Well... its quite simple to get a SQL min date
DateTime sqlMinDateAsNetDateTime = System.Data.SqlTypes.SqlDateTime.MinValue.Value;
Solution 5 - C#
> 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.
Solution 6 - C#
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;
}
Solution 7 - C#
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;
Solution 8 - C#
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. :-)
Solution 9 - C#
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;
Solution 10 - C#
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());