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

Question

I can't seem to find the right answer in other questions like this.

I'm using this piece of code to assign the date that should be saved in the database.

request.BranchRequestDate = DateTime.Now;

And when I'm trying to retrieve the data from the table using this function.

public Request SelectByID()
    {
        try
        {
            SqlCommand command = this.GetParametizedCommand("SELECT * FROM Request WHERE RequestUID = '" + this.RequestUID.ToString() + "' ;");
            command.CommandType = CommandType.Text;
            SqlDataAdapter adapter = new SqlDataAdapter(command);
            DataTable tbl = new DataTable();
            adapter.Fill(tbl); //This is where the exception is thrown
            return ToEntity(tbl);
        }
        catch
        {
            return null;
        }

    }

...It throws and exception.

I've tried checking everything, but what I can't figure out is why it throws and exception, as I can't see what the retrieved data looks like in C#, but I've tried the statement in SQL Server itself and it returns the correct data.

Any idea?

EDIT: I have also compared it with a similar function in the same program, and it functions just fine.

EDIT: Code for GetParametizedCommand method

public SqlCommand GetParametizedCommand(String commandText)
    {
        try
        {
            SqlCommand command = base.GetSqlCommand(commandText);
            if(SavingStatus == Util.SavingStatus.INSERT)
                command.Parameters.Add(new SqlParameter("@RequestUID", SqlDbType.Int,32, ParameterDirection.Output, false, 38, 38, "RequestUID", DataRowVersion.Default, this.RequestUID));
            else if (SavingStatus == Util.SavingStatus.UPDATE || SavingStatus == Util.SavingStatus.DELETE)
                command.Parameters.Add(new SqlParameter("@RequestUID", SqlDbType.Int,32, ParameterDirection.Input, false, 38, 38, "RequestUID", DataRowVersion.Default, this.RequestUID));
            command.Parameters.AddWithValue("@DisplayID", this.DisplayID);
            command.Parameters.AddWithValue("@BranchUID", this.BranchUID);
            command.Parameters.AddWithValue("@EmployeeUID", this.EmployeeUID);
            command.Parameters.AddWithValue("@BranchRequestDate", this.BranchRequestDate);
            command.Parameters.AddWithValue("@IsDeleted", this.IsDeleted);
            return command;
        }
        catch
        {
            return null;
        }

    }

well... do you have any values in your database that could cause this? — RandomUs1r, Apr 24 '13 at 15:57
@RandomUs1r, what do you mean? The only date field in that table is the BranchRequestDate. — Mico Francisco, Apr 24 '13 at 16:00
@MichaelLiu, it just adds parameters to the SQL command for inserting, updating, and deleting. But if the statement is just a SELECT statement, it just adds the parameters but does nothing with them. — Mico Francisco, Apr 24 '13 at 16:03
I see its been answered, but you can also take a look at http://stackoverflow.com/questions/468045/error-sqldatetime-overflow-must-be-between-1-1-1753-120000-am-and-12-31-999 — RandomUs1r, Apr 24 '13 at 16:41

Michael Liu · Accepted Answer · 2013-04-24T16:20:45.190

7

The exception might be happening because this.BranchRequestDate hasn't been initialized when you call GetParametizedCommand with the SELECT statement.

Because this.BranchRequestDate hasn't been initialized, it equals DateTime.MinValue, which isn't a valid SQL datetime value.

You could add an explicit check for DateTime.MinValue and avoid adding the parameter in that situation:

if (this.BranchRequestDate != DateTime.MinValue)
    command.Parameters.AddWithValue("@BranchRequestDate", this.BranchRequestDate);

Or you might create a GetCommand method that doesn't add any parameters at all for a SELECT statement.

edited Apr 24 '13 at 16:20

answered Apr 24 '13 at 16:10

Michael Liu

52,147
13
117
150

2

Where [DateTime.MinValue](http://msdn.microsoft.com/en-us/library/system.datetime.minvalue.aspx) appears to be January 1, 0001, well before the (Gregorian calendar) start date of January 1, 1753 for [SqlDateTime.MinValue Field](http://msdn.microsoft.com/en-us/library/system.data.sqltypes.sqldatetime.minvalue.aspx) – criticalfix Apr 24 '13 at 16:20

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

1 Answers1