2

I have a table with two columns - ID as int and date stored as nvarchar for some reason.

A non-clustered index was recently created on the ID field. Now in my query, when I convert the date from nvarchar to date, it gives me an 'Arithmetic overflow error converting expression to data type datetime.' exception.

If I remove the id field from my query, the conversion works perfectly. Similarly if I remove the date field conversion from my query, it again works perfectly. If I drop the index, the original query works perfectly.

My question now is, why is an index on an ID column messing up conversion in a what-I-think-as-unrelated, date column?

Any explanation on this is much appreciated. And Query is:

select e.date ,e.id as id from dbo.table1 e,dbo.table2 d with (nolock) 
where e.id=d.id and 
convert(datetime,e.date) between convert(datetime,'07/01/2012') 
and convert(datetime,'07/02/2012') group by e.date,e.id
Ram
  • 1,131
  • 10
  • 28
  • 52
etrast81
  • 280
  • 1
  • 4
  • 16
  • I wanted to create a sample tables to show what exactly happens, but when i create two different tables and create the index in it, I am unable to reproduce the issue. But here is the original query anyway, select e.date ,e.id as id from dbo.table1 e,dbo.table2 d with (nolock) where e.id=d.id and convert(datetime,e.date) between convert(datetime,'07/01/2012') and convert(datetime,'07/02/2012') group by e.date,e.id – etrast81 Jul 12 '12 at 04:29
  • Please **do not** put code samples or sample data into comments - since you cannot format it, it's **extremely hard** to read it.... Instead: **update** your question by editing it to provide that additional information! Thank you. – marc_s Jul 12 '12 at 04:57
  • Somewhere in your table you have date values that are invalid. Adding indexes and output fields can give you a different execution plan that will make those rows to be evaluated. – Mikael Eriksson Jul 12 '12 at 05:10
  • @MikaelEriksson - That does make sense. This particular table I am talking about also has some "int" values, which are not really dates, which is why it has been commonly marked as 'nvarchar'. So, based on your comment, what I understand is that, adding an index actually causes the query to try to convert ALL the data in that table, which is why it results in an error. Am I correct? – etrast81 Jul 12 '12 at 05:21
  • It's hard to tell exactly what is goin on. Have a look at the estimated query plans. That might give you a hint but it sure looks like SQL Server is doing a table scan to evaluate the date condition. – Mikael Eriksson Jul 12 '12 at 05:33
  • @MikaelEriksson - your explanation pointed me directly to the issue. The execution plan did change - instead of selecting the date rows and THEN applying the conversion, SQL now selects the matching IDs first and tries to convert all the rows to date. This resulted in an error when it applying the conversion on int valued rows. Can you post your explanation as a comment, so that I can mark it as the answer? Thank you for pointing me in the right direction. – etrast81 Jul 12 '12 at 05:46

2 Answers2

0

etrast81

As i see your query i just suggest you to use the temp. table first convert your date nvarchar to datetime then join it with next table,

i thinks it work

vijay
  • 58
  • 5
0

The date column in your table has invalid date values. Adding indexes and changing what columns are returned changes the query plan for your query so it evaluates the invalid date values.

Mikael Eriksson
  • 136,425
  • 22
  • 210
  • 281