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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

_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
_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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.