error converting data type varchar to float

Есть одна особенность использования оператора CAST в SQL Server, связанная с преобразованием числа к его строковому представлению. Что произойдет, если число символов в числе превышает размер строки? Например,

Следует ожидать, что мы получим сообщение об ошибке. Правильно, вот это сообщение:

Arithmetic overflow error converting numeric to data type varchar.

(«Ошибка арифметического переполнения при преобразовании числа к типу данных VARCHAR».)

Естественно, что мы будем ожидать того же сообщения и при выполнении следующего оператора:

Но нет. В результате мы получим символ «*» вместо сообщения об ошибке. Мы не беремся судить, с чем это связано, однако, однажды мы столкнулись с проблемой диагностики ошибки в коде, в котором впоследствии выполнялось обратное преобразование к числовому типу.

В нашем простейшем примере это будет выглядеть так:

Вот тут-то мы и получаем ошибку:

Syntax error converting the varchar value ‘*’ to a column of data type int.

(«Ошибка синтаксиса при преобразовании значения «*» к типу данных INT».)

Функция Transact-SQL (T-SQL) — процедурное расширение языка SQL, используемое для программирования на стороне сервера в Microsoft Cистема управления реляционными базами данных (СУБД), разработанная корпорацией Microsoft. Язык структурированных запросов) — универсальный компьютерный язык, применяемый для создания, модификации и управления данными в реляционных базах данных. SQL Server и Sybase ASE. Transact-SQL CONVERT ведет себя аналогичным образом.

Преобразование типа money

Денежный тип данных не является стандартным. В SQL Server имеется два денежных типа:

money : диапазон значений от –922,337,203,685,477.5808 до 922,337,203,685,477.5807

smallmoney : диапазон значений от -214 748,3648 до 214 748,3647

Точность обоих типов одна десятитысячная.

Константу типа money можно задать с помощью префикса $, или же использовать преобразование типов, например:

The above code is currently rounding up a field called totaleffort to the nearest.25, for example if i have a value of 78.19 it will round up to 78.25.

I have a new requirement for the value of zero, when the value = 0 then i need to display the text ‘unknown number’ I have attempted to add an additional case statement however the query fails to run with an error :

Error converting data type varchar to float.

Does anyone have a reccomendation for me

5 Answers 5

First of all, your present code returns a number. And you are trying to add a condition when it should return a string. The problem is, numeric types take precedence over string types, and so, as a result, SQL Server will try to convert your string message to a number (and fail).

To avoid that, you should make sure that all numeric values you are returning are properly converted to strings, then you can easily add whatever message you want as a substitute for zeros.

Another thing is, your rounding technique seems to me overcomplicated. If you want to round up, just use CEILING() . If you want to round up to the nearest 0.25 , you can multiply by 4, apply CEILING() , then divide by 4.

Here’s my attempt at illustrating what I mean:

You can also see that I’m using ISNULL() and NULLIF() here to replace 0 with a custom text. It works like this:

the calculation result is passed to NULLIF whose second argument is 0 – that means that if the result is 0 , NULLIF will return NULL , otherwise it will return the result;

now ISNULL does the opposite: it returns the second argument if the first one is NULL , otherwise it returns the first argument.

So, with this chain of transformations a zero effectively becomes ‘unknown number’ .

you can’t expect to have a column where sometimes the value is varchar and and other time float, so you can convert the whole results in THEN to nvarchar like:

look at last line

Assuming you want to add when the value of your condition is 0, them do it like this:

why dont you change (expression) > 0 and (same expression) to (expression) between 0 and 0.25

on the first example you are calculating the same expression twice for no reason

I notice that both the provided answers are doing a conversion in the ‘When’ part of the clause without converting the comparison value to nvarchar as well. That may be why you are still seeing errors with the provided code.

I would suggest that you leave the data type alone in the ‘When’ clause (it appears that the ‘correct’ comparison is numeric), but all the ‘Then’/’Else’ results need converted to character types as SQL can’t mix-and-match data types in the same column.

Just a little extra input. beyond the scope of the question, I realize 🙂 If this is for a report, I suggest altering the report interface instead of altering the SQL. Leaving the data type alone at the view/procedure/function level will make the data structure more reusable/extensible and calculations/aggregates that should use the zero value will behave as expected without having to ‘reverse convert’. If you must change the SQL-side instead of the interface-side, I would suggest including both the ‘report pretty’ and ‘actual value’ columns in the SQL-side structure so you don’t lose any functionality by removing zero values and changing data types.

Error converting data type varchar to float

• 9 shares
• 2
• 3
• 2
• 1
• 0
• 1

Sometimes, under certain circumstances, when you develop in SQL Server and especially when you try to convert a string data type value to a float data type value, you might get the error message: error converting data type varchar to float . As the error message describes, there is a conversion error and this is most probably due to the input parameter value you used in the conversion function.

Read more below on how you can easily resolve this problem.

Reproducing the Data Type Conversion Error

As mentioned above, the actual reason you get this error message, is that you are passing as a parameter to the CAST or CONVERT SQL Server functions, a value (varchar expression) that is invalid and cannot be converted to the desired data type.

Consider the following example:

If you execute the above code you will get an error message in the following type:

Msg 8114, Level 16, State 5, Line 6
Error converting data type varchar to float.

Another similar example where you get the same data type conversion error, is the below:

Why you get this Conversion Error

The exact reason for getting the error message in this case is that you are using the comma (,) as a decimal point and also the dots as group digit symbols. Though SQL Server considers as a decimal point the dot (.). Also when converting a varchar to float you must not use any digit grouping symbols.

Learn more SQL Server Administration tips via live demonstrations and hands-on guides!

Check my online course on Udemy titled “ Essential SQL Server Administration Tips ” (special limited-time discount included in link).

Learn essential hands-on SQL Server Administration tips on SQL Server maintenance, security, performance, integration, error handling and more. Many live demonstrations and downloadable resources included!

How to Resolve the Conversion Issue

In order for the above code to execute, you would need to first remove the dots (that is the digit grouping symbols in this case) and then replace the comma with a dot thus properly defining the decimal symbol for the varchar expression .

Note: You need to be careful at this point, in order to correctly specify the decimal symbol at the correct position of the number.

Therefore, you can modify the code of example 1 as per below example:

If you execute the above code you will be able to get the string successfully converted to float.

Similarly, you can modify the code of example 2 as per below example:

Again, if you execute the above code you will be able to get the string successfully converted to float.

*Note: Even though you can try changing the regional settings of the PC for setting the dot (.) as the decimal symbol, this will only affect the way the data is presented to you when returned from the casting/conversion call. Therefore, you still have to modify the varchar expression prior to the casting/conversion operation.

Regarding the message: error converting data type varchar to numeric

The above error message is similar to the one we examined in this article, therefore, the way for resolving the issue is similar to the one we described in the article. The only different for the numeric case, is that you will have to replace FLOAT with numeric[ (p[ ,s] )]. Learn more about the numeric data type in SQL Server and how to resolve the above conversion issue, by reading the relevant article on SQLNetHub.

Other related SQL Server troubleshooting articles to check on SQLNetHub:

Check our Online Courses:

Recommended Software Tools

Snippets Generator : Create and modify T-SQL snippets for use in SQL Management Studio, fast, easy and efficiently.

Dynamic SQL Generator : Convert static T-SQL code to dynamic and vice versa, easily and fast.

Learn How to Become a Great Programmer!

Online Course (Lifetime Access): The Philosophy and Fundamentals of Computer Programming”

Learn the philosophy and main principles of Computer Programming and get introduced to C, C++, C#, Python, Java and SQL.

Subscribe to our newsletter and stay up to date!

Rate this article: (16 votes, average: 5.00 out of 5)