在 SQL Server 2005 Express 中将 NVARCHAR 转换为 INTEGER 时出现问题

发布于 2024-09-25 12:55:16 字数 713 浏览 5 评论 0原文

大家下午好，

我在将计算转换为整数时遇到一些问题！基本上我有计算；

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)

（它根据邮政编码计算经度和纬度之间的距离）

现在，我可以在 SELECT 语句中使用该值，以及输出<的 ISNUMERIC() /strong> 返回 true！

但是，当我尝试在 WHERE 语句中限制此值 WHERE .... <= 150 时，我收到返回的“将 NVARCHAR 转换为整数时出错”信息。

有人可以帮忙吗？我已经尝试过 CONVERT 和 CAST，如果我添加有限的 WHERE ISNUMERIC(...) = 1 我没有得到任何输出值！

感谢所有帮助

最亲切的问候皮特·W

原文

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

甜是你 2024-10-02 12:55:16

我用常量值（5 和 6）替换了您的表引用（tblPostCode.PCLng、tblPostCode.PCLat）来测试您的查询。正如马丁史密斯在他的评论中已经提到的，引号中的表达是完全无效的。这可能是以前查询的遗留物吗？ sql 的作用是尝试将表达式转换为整数，但由于引号中的“&”而失败。如果您删除标记并保留恒定值，它对我有用：

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)

结果是：

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:

回复收藏 0 原文

~没有更多了~