Using SQL 2005 trying to cast 16 digit Varchar as Bigint error converting

First, thanks for all your help! You really make a difference, and I GREATLY appreciate it.

So I have a Varchar column and it holds a 16 digit number, example: 1000550152872026

select *
FROM Orders 
where isnumeric([ord_no]) = 0

returns: 0 rows

---

select cast([ord_no] as bigint)
FROM Progression_PreCall_Orders o
order by [ord_no]

returns: Error converting data type varchar to bigint.

How do I get this 16 digit number into a math datatype 开发者_运维问答so I can add and subtract another column from it?

UPDATE: Found scientific notation stored as varchar ex: 1.00054E+15

How do I convert that back into a number then?

---

DECIMAL datatype seems to work fine:

DECLARE @myVarchar AS VARCHAR(32)
SET @myVarchar = '1000550152872026'

DECLARE @myDecimal AS DECIMAL(38,0)
SET @myDecimal = CAST(@myVarchar AS DECIMAL(38,0))
SELECT @myDecimal + 1

Also, here's a quick example where IsNumeric returns 1 but converting to DECIMAL fails:

DECLARE @myVarchar AS VARCHAR(32)
SET @myVarchar = '1000550152872026E10'
SELECT ISNUMERIC(@myVarchar)

DECLARE @myDecimal AS DECIMAL(38,0)
SET @myDecimal = CAST(@myVarchar AS DECIMAL(38,0)) --This statement will fail

EDIT
You could try to CONVERT to float if you're dealing with values written in scientific notation:

DECLARE @Orders AS TABLE(OrderNum NVARCHAR(64), [Date] DATETIME)
INSERT INTO @Orders VALUES('100055015287202', GETDATE())
INSERT INTO @Orders VALUES('100055015287203', GETDATE())
INSERT INTO @Orders VALUES('1.00055015287E+15', GETDATE()) --sci notation

SELECT 
    CONVERT(FLOAT, OrderNum, 2) + 
    CAST(REPLACE(CONVERT(VARCHAR(10), GETDATE(), 120), '-', '') AS FLOAT)
FROM @Orders

---

WITH validOrds AS
(
    SELECT ord_no
    FROM Orders 
    WHERE ord_no NOT LIKE '%[^0-9]%'
)
SELECT  cast(validOrds.ord_no as bigint) as  ord_no
FROM    validOrds
        LEFT JOIN Orders ords
            ON ords.ord_no = validOrds.ord_no
WHERE   ords.ord_no is null             

Take a look at this link for an explanation of why isnumeric isn't functioning the way you are assuming it would: http://www.sqlservercentral.com/articles/IsNumeric/71512/

Take a look at this link for an SO post where a user has a similar problem as you: Error converting data type varchar

hence, you should always use the correct datatype for each column unless you have a very specific reason to do so otherwise... Even then, you'll need to be extra careful when saving values to the column to ensure that they are indeed valid values