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