SQL转换NTEXT

SQL转换NTEXT - > VARCHAR - > FLOAT并有所作为(SQL converting NTEXT -> VARCHAR -> FLOAT and doing a difference)

我有两个字段，一个是int，另一个是ntext。 我需要从int字段中减去ntext字段。 我正在使用Hours_Current这是int字段并将它潜水60.我已经调试了一段时间并且可以确认第一个CAST正在工作并按预期除以60。 然后我将nText字段转换为float并从小时减去时间。 DB中的Time字段有一些条目是数字（我需要的），有些条目是文本（不需要）。

SELECT CAST(SUM(Hours_Current) AS FLOAT)/60 AS 'Current Hours' CONVERT(FLOAT, CONVERT(VARCHAR(MAX), ObjField)) AS 'Time', 'Time' - 'Current Hours' AS DIFF

我已经尝试过无数种不同的方法来实现这一目标。 大多数时候我得到“操作数数据类型varchar对减法运算符无效”。 我的最终目标是在报告中使用它。 我还取出了DIFF并使用SSRS表达式尝试减去这两个字段，并在报告中得到#Error。

我已经看过这个链接，并尝试过，但似乎没有用。 我看了一下网上的内容，我发现的一些文章想要讨论ntext如何折旧。 遗憾的是我无法更改数据库架构。 有任何想法吗？

我也尝试使用nvarchar而不是varchar。

I have two fields, one that is int and one that is ntext. I need to subtract the ntext field from the int field. Im taking Hours_Current which is the int field and diving it by 60. I've debugged this for a while and can confirm that the first CAST is working and dividing by 60 as intended. Then I'm converting the nText field to float and subtracting time from hours. The Time field in the DB has some entries that are numbers (what I need) and some entries are text (don't need).

SELECT CAST(SUM(Hours_Current) AS FLOAT)/60 AS 'Current Hours' CONVERT(FLOAT, CONVERT(VARCHAR(MAX), ObjField)) AS 'Time', 'Time' - 'Current Hours' AS DIFF

I've tried countless different ways to accomplish this. Majority of the time I get a "Operand data type varchar is invalid for subtract operator". My end goal is to use this in a report. I've also taken out the DIFF and used SSRS expression to try to subtract the two fields and I get a #Error on the report.

I've seen this link and have tried that but it doesn't seem to work. I've looked and looked online and what few articles I have found want to talk about how ntext is depreciated. I unfortunately can't change the DB schema. Any ideas?

I have also tried to use nvarchar instead of varchar.

最满意答案

您不能在同一select子句中的select子句中使用别名。 您必须执行两次计算或使用cte / derived表：

;WITH CTE AS ( SELECT CAST(SUM(Hours_Current) AS FLOAT)/60 AS Current_Hours CONVERT(FLOAT, CONVERT(VARCHAR(MAX), ObjField)) AS [Time] FROM ... WHERE ... ) SELECT Current_Hours As [Current Hours], [Time], [Time] - Current_Hours As [Diff] FROM CTE

要么

SELECT CAST(SUM(Hours_Current) AS FLOAT)/60 AS [Current Hours] CONVERT(FLOAT, CONVERT(VARCHAR(MAX), ObjField)) AS [Time], (CAST(SUM(Hours_Current) AS FLOAT)/60) - (CONVERT(FLOAT, CONVERT(VARCHAR(MAX), ObjField))) As [Diff] FROM ...

You can't use the aliases in the select clause inside the same select clause. You must either do the calculation twice or use a cte/derived table:

;WITH CTE AS ( SELECT CAST(SUM(Hours_Current) AS FLOAT)/60 AS Current_Hours CONVERT(FLOAT, CONVERT(VARCHAR(MAX), ObjField)) AS [Time] FROM ... WHERE ... ) SELECT Current_Hours As [Current Hours], [Time], [Time] - Current_Hours As [Diff] FROM CTE

OR

SELECT CAST(SUM(Hours_Current) AS FLOAT)/60 AS [Current Hours] CONVERT(FLOAT, CONVERT(VARCHAR(MAX), ObjField)) AS [Time], (CAST(SUM(Hours_Current) AS FLOAT)/60) - (CONVERT(FLOAT, CONVERT(VARCHAR(MAX), ObjField))) As [Diff] FROM ...