Why does this SqlCommand parameter produce SQLDateTime overflow error?

Question

I have these blocks of code:

var cmd = new SqlCommand (); 
cmd.CommandText = @"SELECT * FROM " + TableName + " Where ";
SqlConnection a = new SqlConnection("the setting");
cmd.Connection = a;

These lines below work fine:

cmd.CommandText += strFromTextBox + " LIKE '%" + strUserInput + "%'";
//strFromTextBox & strUserInput is string datatype
//it looks like 'apple', 'ladder', just normal string
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = cmd;

But these ones produced error:

DateTime dtFrom = DateTime.Parse(dt1).Date;
DateTime dtTo = DateTime.Parse(dt2).Date;
//dt1 & dt2 is originally a string
//they look something like this: 2/1/2012 12:00:00 AM
cmd.Parameters.AddWithValue("@dt1", @dtFrom); 
cmd.Parameters.AddWithValue("@dt2", @dtTo);
cmd.CommandText+= parameter[i].ToString() + " BETWEEN @dt1 AND @dt2";
//parameter[i] refers to the column name

The error it produced:

System.Data.SqlTypes.SqlTypeException: SqlDateTime overflow.

Both blocks are processed through:

DataTable Table = new DataTable();
adapter.FillSchema(Table, SchemaType.Source);
adapter.Fill(Table);

The final query output (found during character)

SELECT * FROM linkDb Where CreateDt BETWEEN @dt1 AND @dt2

Can someone advises whats wrong with those lines of code?

obviously not, pls do check the blocks of code i provide.. i put one commented line to show sample of the inputs.. — rofans91
– rofans91, Commented Jan 6, 2012 at 6:23
I have done so. TQ. In fact I have other friend helping me to check the code too.. It's not from the input.. — rofans91
– rofans91, Commented Jan 6, 2012 at 6:29
It refers to column name, i have edited it.. thank you for spotting it.. — rofans91
– rofans91, Commented Jan 6, 2012 at 6:41
@marc_s I edited my question, can't put here due to the ampersand character not allowed to typed more than 1 in comment.. — rofans91
– rofans91, Commented Jan 6, 2012 at 7:21

Quick Joe Smith · Accepted Answer · 2012-01-06 06:22:53Z

2

The SQL Server DATETIME datatype can only represent dates from 00:00:00 1/1/1753 to 23:59:59 31/12/9999. My guess is that one of the date strings is either being given a date outside this range, or it is being parsed differently than you think.

Also, building SQL strings like that is hugely prone to SQL injection. You should consider abandoning this practice very soon. Like, now.

answered Jan 6, 2012 at 6:22

Quick Joe Smith

8,2683 gold badges31 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

rofans91 Over a year ago

I know. But please do check my question throughly I did provide example of the input... which is lying between the normal timeline.. //they look something like this: 2/1/2012 12:00:00 AM the error looks contrasting with my input that's why...

Quick Joe Smith Over a year ago

As marc_s already said: check your assumptions. Something I've learned is that when reality is clashing with your expectations, reality is usually correct.

Akash Kava Over a year ago

Unless there is a bug in SQL server or .net base class library (which is highly impossible as being used by millions), error is in the input, date string is null or empty resulting in DateTime.MinValue which is outside SQL range.

RickNZ · Accepted Answer · 2012-01-07 08:11:49Z

Instead of this:

cmd.Parameters.AddWithValue("@dt1", @dtFrom); 
cmd.Parameters.AddWithValue("@dt2", @dtTo);

Try this:

SqlParameterCollection p = cmd.Parameters;
p.Add("@dt1", SqlDbType.DateTime).Value = dtFrom;
p.Add("@dt2", SqlDbType.DateTime).Value = dtTo;

I would also set the value of CommandText before setting the parameters.

If that fails as well, you should use a debugger to verify the values contained in dtFrom and dtTo.

BTW, I know it wasn't part of your question, but your posted code is susceptible to SQL injection. You should also wrap your SqlConnection and SqlCommand objects with using statements.

Collectives™ on Stack Overflow

Why does this SqlCommand parameter produce SQLDateTime overflow error?

2 Answers 2

3 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Related