As we all know, there are various numeric data types, such as TINYINT, INT, SMALLINT, BIGINT, NUMERIC, DECIMAL, SMALLMONEY, MONEY and FLOAT.
I was working on a SSIS package, which had staging table with all varchar columns. The files were loaded into the staging table, gets validated for the data type, length etc. and the valid records were then moved to the final tables. Final table had the actual numeric data types.
The source file had empty string (”) for the columns with no values, including in the numeric columns. I’ve noticed a strange behavior of ” with numeric data types. Although for most of the numeric columns, ” was treated as 0. SQL Server was converting the ” to 0. But for few numeric columns, it was failing with error “Error converting data type varchar to numeric”.
It surprised me! I started digging and here what I found.
Except NUMERIC and DECIMAL data types, all other numeric data types mentioned above were treating the empty string (”) as 0. But these two data types – NUMERIC and DECIMAL were throwing the error.
I tried to find out the cause of this strange behavior, but no luck! May be did not used the right keywords while googling.
You can try it out yourself using the following query. Do let me know if you found any reference proving the cause of this strange behavior!
I fixed the error by using NULLIF function to treat empty string (”) as NULL.
DECLARE @TINYINT AS TINYINT = '' DECLARE @INT AS INT = '' DECLARE @SMALLINT AS SMALLINT = '' DECLARE @BIGINT AS BIGINT = '' DECLARE @SMALLMONEY AS SMALLMONEY = '' DECLARE @MONEY AS MONEY = '' DECLARE @FLOAT AS FLOAT = '' DECLARE @NUMERIC AS NUMERIC = '' DECLARE @DECIMAL AS DECIMAL = ''
.