Behavior of numeric data types with ”

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

.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s