Problems converting NVARCHAR to INTEGER in SQL Server 2005 Express

Question

Afternoon everyone,

I'm having some issues converting a calculation into a INTEGER! Essentially I have the calculation;

CAST(ROUND(SQRT(SQUARE(69.1*(CAST(tblPostCode.PCLat AS DECIMAL(30,15)) - "& 53.078282 &")) + SQUARE(69.1 * (CAST(tblPostCode.PCLng AS DECIMAL(30,15)) - "& -2.271495 &") * COS(CAST(tblPostCode.PCLat AS DECIMAL(30,15))/57.3))),0) AS INTEGER)

(It calculated the distance between longitude and latitudes from post codes)

Now, I can use this value in the SELECT statement, and ISNUMERIC() of the output returns true!

HOWEVER, when I try and limit on this value in the WHERE statements, WHERE .... <= 150, I get a returned "Error converting NVARCHAR TO INTEGER" message.

Can anyone help? I've tried CONVERT and CAST and if I add the limited WHERE ISNUMERIC(...) = 1 I get no output values!

All help is appreciated

Kindest Regards Pete W

Answer 1

I replaced your table references (tblPostCode.PCLng, tblPostCode.PCLat) with constant values (5 and 6) to test your query. As Martin Smith already mentioned in his comment the expression in quotation marks is completely invalid. Is this a relict of a former query perhaps? What sql does is trying to convert the expression to integer which fails due to the "&"s in the quotation marks. If you remove the marks and keep the constant values, it worked for me:

SELECT  CAST(ROUND(SQRT(SQUARE(69.1
                               * ( CAST(5 AS DECIMAL(30, 15))
                                   - 53.078282 )) + SQUARE(69.1
                                                              * ( CAST(6 AS DECIMAL(30,
                                                              15))
                                                              - 2.271495 )
                                                              * COS(CAST(5 AS DECIMAL(30,
                                                              15)) / 57.3))),
                   0) AS INTEGER)

The result is:

3332