I was having trouble loading some data into a SQL Server database (MS). The particular problem was converting a type char to type real.
There are several ways to change data types. Typically, you'd use cast or convert functions. But everything I tried returned the same message: "Error converting data type varchar to real."
I googled to find out how other people dealt with this, but found nothing useful.
Then I tried something that worked: convert the type varchar to type money, then to type real. Easy.
Of course, this makes perfect sense if your numbers only run to two decimal places. In fact, it works to four decimal places (the same if you choose type money or smallmoney, except that the latter is stored in fewer bytes).
The options for retaining precision past four decimal places are somewhat messier. One way is to multiply the numbers by a few factors of ten, but you still have to pay attention to the range of viable values for money or smallmoney.
I'm not convinced that this issue is yet another demonstration of the unwieldiness of MS SQL Server. All databases have their pain points; this is just another one.
3 comments:
Brilliant! It worked! I was struggling with the same issue, googling with the same lack of results as you, until I hit upon your post. Thanks!
Glad to help.
I posted simply because I couldn't google the solution quickly enough, so worked something out.
Next problem: bulk loading in SQL Server 2005 Express in Vista. SSIS is out (Express), the Jet OLEDB is now out, and bcp hasn't been playing ball with me. Yet.
Thank you - I had the same issue and found the solution in your post as well!
Post a Comment