5

I have a table with a field of type NTEXT which stores many type of values, filesize among them. I'm trying to run a query on a list of records and add up the file sizes but I'm encountering this perplexing problem.

Since NTEXT cannot be directly/implicitly converted to INT or BIGINT, I'm converting it first to VARCHAR then I'm trying to convert it to either INT or BIGINT. All goes fine until I try to convert the VARCHAR value to INT or BIGINT.

Here are my queries and results:

First I try the following, which shows no problems and the output is 61069 (value still as ntext type).

SELECT FileSize
FROM dbo.myTable
WHERE ID = 111

Now I convert/cast it as varchar, and again, no problem. The output is 61069 (now varchar type).

SELECT CONVERT(VARCHAR, FileSize)
FROM dbo.myTable
WHERE ID = 111

Finally, I try to convert the VARCHAR value into BIGINT so that I can do my SUM() and other calculations, but this time I get a "Error converting data type varchar to bigint." message.

SELECT CONVERT(BIGINT, CONVERT(VARCHAR, FileSize))
FROM dbo.myTable
WHERE ID = 111

And if I try converting it to INT instead, I get a "Conversion failed when converting the varchar value '7/1/2008 3:39:30 AM' to data type int"

SELECT CONVERT(INT, CONVERT(VARCHAR, FileSize))
FROM dbo.myTable
WHERE ID = 111

I'm absolutely lost, any ideas of what could be causing this?

sergeidave
  • 662
  • 4
  • 11
  • 23
  • @GSerg not a fan of the duplicate. It uses a much more generic check against `ISNUMERIC` which will potentially match against more rows and will also potentially identify values that pass the `ISNUMERIC` check but can't be converted to `INT`. Try `SELECT ISNUMERIC('5e6');` - now try converting that string to an `INT`. In other words, if *this* user uses *that* answer they may still get the error message. That may work fine for `FLOAT` but there may be cases that fail there, too. I'd be careful relying on `ISNUMERIC` for much. – Aaron Bertrand Feb 11 '13 at 16:17

1 Answers1

6

You can't control the order in which the where clause and conversions apply. In some cases SQL Server will attempt to perform the conversion on rows that wouldn't pass the filter - all depends on the plan. Try this instead:

SELECT CASE WHEN ID = 111 THEN 
  CONVERT(INT, CONVERT(VARCHAR(12), FileSize))
  -- don't be lazy, size ------^^ is important
  END
FROM dbo.myTable
WHERE ID = 111;

Also consider using an integer column to store integers. Then you don't end up with goofy nonsense in the FileSize column like '7/1/2008 3:39:30 AM'.

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490