Data Type Changing in UNION

Standard

An interesting issue came up recently where a union was creating a rounding problem. One side of the UNION contained a field with data type NUMBERIC(38,2) and the other side, (the equivalent field) was DECIMAL(24,9). One field having the bigger precision (number of digits to the left of the decimal point) and the other have a bigger scale (number of digits to the right of the decimal point)..

Let’s  see it in action..

SELECT CAST(GETDATE() AS NUMERIC(38,2)) [Test1]

Result:

dt1

SELECT CAST(GETDATE() AS DECIMAL(24,9)) [Test2]

Result:

dt2

And when we union them together: 

SELECT CAST(GETDATE() AS NUMERIC(38,2)) [Test3]

UNION ALL

SELECT CAST(GETDATE() AS DECIMAL(24,9)) [Test3]

Result:

dt3

The answer lies in the fact that SQL Server will preserve the biggest scale over the biggest precision. After all I’d be far more annoyed if I lost £1000 that .0120144414324p..

The solution to this problem is to ensure that you cast to the same data type in both of your queries to ensure you know the data types that will come out of the other end.

Advertisements

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s