SqlDateTime overflow error with parsing Date in SQL Server

Question

I am trying to save date from C# to SQL Server. First want to display Date format as dd/MM/yyyy to the user. then after selecting the date on Winforms screen. I want to save it to database. If I remove datetimePicker1.CustomFormat line in the code it is saving fine to the database. But I want to display the date format as dd/MM//yyyy. How to solve this?

I'm getting this error:

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

Code:

//c#   
DateTime fromDate;

public void SetMyCustomFormat()
{
    // Set the Format type and the CustomFormat string.
    //dateTimePicker1.Format = DateTimePickerFormat.Custom;
    dateTimePicker1.CustomFormat = "dd/MM/yyyy";

    DateTime.TryParse(dateTimePicker1.Text, out fromDate);
    fromDate = fromDate.Date;            
}

Answer 1

You didn't include any code of where you are using this value with respect to SQL Server, however the error is likely due to the D/M/Y format. This will cause a problem on, for example, Dec 31 because it will be passed as text 31/12/2014 which typically causes problems when converting to a date (depending on locale settings).

For your case just use the DateTimePicker.Value property to extract the date. This will return a DateTime type so you don't have to parse the value.

DateTime fromDate;
public void SetMyCustomFormat()
{
    // Set the Format type and the CustomFormat string.
    dateTimePicker1.CustomFormat = "dd/MM/yyyy";
    fromDate = dateTimePicker1.Value.Date;            
}

Answer 2

The Sql DateTime and the C# DateTime types have different valid date ranges (hence they aren't fully compatible).

Sql Datetime only support January 1, 1753, through December 31, 9999.

The issue is that your TryParse is failing causing fromDate to be 1/1/0001 which the Sql DateTime type doesn't support.

In SQL use DateTime2 and always validate the success of the parse.

http://msdn.microsoft.com/en-us/library/ms187819.aspx

http://msdn.microsoft.com/en-us/library/bb677335.aspx

UPDATED:

And the reason your TryParse is failing is because it is expecting the format mm/dd/yyyy. Instead of using TryParse use:

bool success = DateTime.TryParseExact(dateTimePicker1.Text,
                   "dd/MM/yyyy",
                   CultureInfo.InvariantCulture,
                   DateTimeStyles.None,
                   out fromDate);