Beware of an Odd Problem with CAST() and CONVERT()

Document Type: Tip

Released: 2024-11-22

Here's an odd problem - something that doesn't occur too commonly in the wild.

But, when it does, it can cause some REALLY odd problems that can make you think you're losing you're mind.

Easy-Peasy, Lemon-Cast-y

Consider the following:

SELECT CONVERT(varchar(10), 1234567890) [output];
SELECT CAST(1234567890 AS varchar(10)) [output];

No surprises with the output:

Toxic Negativity

But, something odd happens when you try the same thing with NEGATIVE integers that are the exact same size as the string you’re trying to cast/convert to:

-- Negative (and LEN() > varchar(10))... 
SELECT CONVERT(varchar(10), -1234567890) [output];
SELECT CAST(-1234567890 AS varchar(10)) [output];

The results just aren’t at all what you’d expect:

Specifically, I would’ve expected either:

• overflow errors
  • or
• numbers (expressed as strings)

Instead, you get neither.

I’ve not seen this behavior documented anywhere - but it’s consistent (i.e., easy to reproduce).

Bonus Points

And, of course, if you’re wondering, attempting to CAST() or CONVERT() POSITIVE numbers that are too ‘wide’ or ‘long’ for the string length you’re targeting, WILL overflow - e.g.,

-- overflow:
SELECT CONVERT(varchar(10), 123456789012) [output];
SELECT CAST(123456789012 AS varchar(10)) [output];

produces results like the following:

Likewise, if you try NEGATIVE numbers that are ‘wider’ than your target, you’ll also get overflow errors.

In short, this behavior only happens when:

• the integer you’re casting/converting is ‘exactly’ the SAME length as your target string
• AND the integer is NEGATIVE.

Or, more specifically:

• Positive integers the same length convert without issue.
• Negative integers the SAME LENGTH as the target string are problematic.
• Positive AND negative integers LONGER than your target length throw/overflow.
• Positive AND negative integers SHORTER than your target length convert as expected.