Geek asks and answers

T-SQL: Converting NTEXT to VARCHAR to INT / BIGINT

I have a table with a field of type NTEXT , which stores many types of values, the file size among them. I am trying to run a query in a list of entries and add file sizes, but I ran into this problem.

Since NTEXT cannot be directly / implicitly converted to INT or BIGINT , I first convert it to VARCHAR strong>, then I try to convert it to INT or BIGINT. Everything 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 (the value is still like a text type).

SELECT FileSize
FROM dbo.myTable
WHERE ID = 111

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

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

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

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

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

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

I'm completely lost, any ideas on what could be causing this?

+5
1

, where . SQL Server , - . :

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;

. FileSize, '7/1/2008 3:39:30 AM'.

+6

More articles:


All Articles