When running the following query I get the error message 'Conversion failed when converting the varchar value '3/23/2017' to data type int.' Can you please assist?

When running the following query I get the error message 'Conversion failed when converting the varchar value '3/23/2017' to data type int.'  Can you please assist?

Select [System Name], [Days Overdue], [Status]  
            From dbo.[Monroe_report_3_14_18]
            Where  [Days Overdue] > 90
Yalonda WilliamsAsked:
Who is Participating?
 
_agx_Commented:
(Wrote this up before I saw the other responses, but  in case it helps ...)

Edit: just went through similar data type issues with the Import Wizard myself.  From what I could tell, the wizard uses the cell formats in Excel to "guess" what data types to use for the target columns.  Though you can still edit the types, within the import wizard (to a degree ... like Olaf said, there are limitations).

I created a test spreadsheet with these 4 columns.  I formatted [Date Initiated] as a "date > *3/14/2012" and [Days Overdue] as "number".
Sample SpreadsheetThen ran the Import wizard and it automatically selected these data types.  Though again, you can also change them by clicking "Edit Mappings".
 Import Wizard
2
 
Olaf DoschkeSoftware DeveloperCommented:
Most likely [Days Overdue] should be a number of days, but instead os a date or even just a string representing a date, so something went wrong in generating dbo.[Monroe_report_3_14_18].

Bye, Olaf.
1
 
Mark WillsTopic AdvisorCommented:
well it looks like [days overdue] is a string (varchar) representation of a date, and of course, cannot be converted implicitly to the integer it is being compared to.

I agree with Olaf's summation...

If you can show how that table is being created, we can probably help.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Yalonda WilliamsAuthor Commented:
The table was created in excel and I just imported it from excel to sql server management studio using the import wizard.
0
 
_agx_Commented:
You need to decide what the [Days Overdue]  column should contain: a date or a number.  If "number", you need to fix the data in the spreadsheet and re-import. If a "date", you need to change the SQL query. Since you're comparing to a date, the right hand side of the comparison should also be a date.  For example, find where the date value is <= 90 days ago

        -- ie As of today,  WHERE [your column] <=  '2018-01-05'
         Where  [Column Name Containing Your Date] <= cast(dateAdd(d, -90, getDate()) as date)  

There's an added complication. Currently the column type is string. To avoid unexpected results, it's be best to either pre-format the Excel column as a date and/or select the correct data types in the import wizard.  If the column stores a date string, set the target data type to "date" or "datetime".  That'll ensure the expected results in your sql query.
0
 
Olaf DoschkeSoftware DeveloperCommented:
Oh the strangeness of Excel. As Excel handles dates as numbers, and numbers as dates, you might have a conversion error there, the import has taken a number of days as a date. That's not what you would like to have, so you may need to change your import process.

Or you indeed have a date, but want to have a where clause testing whether that date is longer ago than 90 days. Then you have to have a date difference, using DateDiff(), and using today/now, which is GetDate() as comparison datetime.

Even in that situation the import didn't do its job well, as a date was taken as varchar instead of a date. So you'll need to look into that import, if you want that to become a regular job working without further intervention.

Bye, Olaf.
0
 
Mark WillsTopic AdvisorCommented:
If coming from Excel, then do you also have the baseline date from which [Days Overdue] is being calculated from ?

Also, when importing from Excel, why is [Days Overdue] being imported as varchar ?

How are you importing ?
0
 
Yalonda WilliamsAuthor Commented:
I'm importing using the wizard. The [Days Overdue] are numbers not a date.  When even I try changing the data type in excel it still doesn't work.  There are columns within the excel spreadsheet with dates one is labeled [Date Initiated] and the other is [Scheduled Date].  Could these columns be the reason I'm getting the error?
0
 
Olaf DoschkeSoftware DeveloperCommented:
If the days overdue is computed from these two dates you might have it easier to recalculate that difference. You might have a problem of the import wizard inferring the type wrongly from the formula used in that Excel column working on dates.

Check out which columns come over correctly. It's long ago since I used a wizard, but it also might help you copy over the data itself into a new sheet and import that instead of importing from a sheet containing calculated columns with formulas.

Bye, Olaf.
0
 
Yalonda WilliamsAuthor Commented:
I will try that.  Thank you
0
 
Olaf DoschkeSoftware DeveloperCommented:
One thing, which could also cause using the wrong type is a date in the first row of the Excel sheet, anything that differs in the first row (and isn't taken just as a header or name) can cause such import problems. It's just an Excel sheet may have any type of value in every single cell, while a table only has one type. In such cases, the import wizard might also decide for a varchar column, because a char column, of course, can import all you can see (as long as it's not something like a graph or image object in Excel). Anyway, imports have their limits.
1
 
Yalonda WilliamsAuthor Commented:
Ok thank you
0
 
Mark WillsTopic AdvisorCommented:
when excel does a diff between two dates then the underlying column can end up being a date

if you are importing [Date Initiated] and  [Scheduled Date] then you can do your own datediff to get days.

Or, in excel make sure the dates are dateformat and integers are number format. You can edit mappings in the Wizard.

This imports OK for me (d/m/y format in Aus)
datediff.xlsx
0
 
Mark WillsTopic AdvisorCommented:
Haha... Was doing the same thing as _agx_ and then saw his post :)
2
 
_agx_Commented:
Heh... last week's headaches of working with the Import "Wizard" are still with me.  Figured someone should benefit from my pain ;-)
2
 
Yalonda WilliamsAuthor Commented:
Thank you all for your help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.