------------------------------------------------------------------------------------------------------------------------------------------
TITLE: Beware of an Odd Problem with CAST() and CONVERT()
CANONICAL URI: https://www.totalsql.com/sql-server-content/tips/beware-of-an-odd-problem-with-cast-and-convert
AUTHOR: Michael K. Campbell 
COPYRIGHT: 2026 All Rights Reserved: OverAchiever Productions, LLC. 
RELEASED: 2024-11-22
------------------------------------------------------------------------------------------------------------------------------------------

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

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: 

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

No surprises with the output: 

![Just as you'd expect](https://assets.overachiever.net/ts/tips/beware_of_an_odd_problem_1.png) 

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

```sql
-- 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: 

![Wait... what??? ](https://assets.overachiever.net/ts/tips/beware_of_an_odd_problem_2.png)

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

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

produces results like the following: 

![Overflow - as expected.](https://assets.overachiever.net/ts/tips/beware_of_an_odd_problem_3.png)

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.