-1

I wanted to double check my logic for a query in SQL Server.

The idea is that I am able to feed the following values and it will make sure the result is a decimal with four trailing digits.

Possible values for @LABORQTY:

1,200

1,200.42

1200 (Integer)

1200.42

1200 (As a String)

1200.42 (As a String)

When the value is a string, it will give the error: Error converting data type nvarchar to numeric.

Here is my code:

CONVERT(DECIMAL(12, 4), REPLACE(@LABORQTY, ',', ''))

The output each time though should be decimal: 1200.4200

C. Ayers
  • 53
  • 1
  • 9
  • 1
    Can you format your post so it makes a bit more sense? Is ` 1,200 / 1,200.42 / 1200 (Integer) / 1200.42 / 1200 (As a String) / 1200.42 (As a String)` some kind of multiple division formula ? – Caius Jard Jan 23 '19 at 09:31
  • How does @laborqty, which is a named variable, which has a specific and single type, manage to variably be a string, int, decimal.. ? How does one do `DECLARE @laborqty INT` and then store a string decimal "1200.42" ? – Caius Jard Jan 23 '19 at 09:32
  • So what's the actual value of `@LABORQTY` for which this fails? It "is a string" (`nvarchar`) in all cases, that's not specific enough. If you've declared `@LABORQTY` as any other type, the error is probably occurring as you try to assign it unsupported values like `1,200` which get implicitly converted, not your explicit conversion. – Jeroen Mostert Jan 23 '19 at 09:36
  • It's not declared through SQL but through another application. The type is set to Default which in the program is String `nvarchar`. Apologies for the confusion with the slash. When typing it out it wasn't putting the values in a new line and I needed a separator. I didn't expect others would identify it as more division than separation of numbers. My mistake there. – C. Ayers Jan 23 '19 at 18:20

2 Answers2

2

Your question is really confused, but I'll answer according to the following parameters:

@laborqty is a VARCHAR

@laborqty may somehow come to contain any of the following values:

'1200'
'1200.42'
'1,200'
'1,200.42'

In which case CONVERT(DECIMAL(12, 4), REPLACE(@LABORQTY, ',', '')) will indeed produce a decimal with up to 4 digits of fractional precision. Whether your query tool/programming language will output it as 1200.4200 or not is another matter entirely; it might well just output 1200.42 and drop the trailing zeroes

If you're getting Error converting data type varchar to numeric. still, there is some other character data (not comma) in your numeric string

If you definitely want the trailing zeroes, format it into a string before you output

FORMAT(CONVERT(decimal(12,4), '1200.42'), '0.0000')

This will generate a string with 4 trailing zeroes

Caius Jard
  • 72,509
  • 5
  • 49
  • 80
  • What's with all the `#`? The format string will naturally "overflow" if there are more digits, so `0.0000` is enough. (If there are more than will fit in the `decimal`, an overflow happens before the formatting.) Also, it's worth noting that the actual separator used depends on the culture in place -- if this is not desired, better specify `en-US` explicitly. – Jeroen Mostert Jan 23 '19 at 09:46
  • As far as I'm aware FORMAT is based on .NET's formatting logic, and use of `.` means "place the culture sensitive decimal separator here". As such, using a format of 0.0000 should, in a country that uses `,` as the decimal separator, produce an output of `0,0000` - see https://learn.microsoft.com/en-us/dotnet/standard/base-types/custom-numeric-format-strings?view=netframework-4.7.2#SpecifierPt – Caius Jard Jan 23 '19 at 10:05
  • Yes, that was my point exactly. From the OP's question it's not possible to tell if they want formatting specific to the current culture, so I pointed out that you can pass an additional argument to `FORMAT` to specify it. (More often than not, people actually *don't* want the formatting to vary based on culture, and they are surprised when a literal `.` turns into `,`.) – Jeroen Mostert Jan 23 '19 at 10:08
  • OK, well.. These comments contain some handy advice that the OP and future visitors can use, or they can look up FORMAT if they want.. I think there has to come a limit where one stops adding detail to an answer lest it come to completely replicate the contents of MSDN.. Perhaps we have different thresholds of "how much of the documentation should be read out to the OP" :) (See also https://learn.microsoft.com/en-us/sql/t-sql/functions/format-transact-sql?view=sql-server-2017 ) – Caius Jard Jan 23 '19 at 10:08
0

you can use :

select CAST ( REPLACE( '1,200.4' , ',','') AS decimal(17,4))
Ali Fidanli
  • 1,342
  • 8
  • 12