CODEWITHSUNDEEP
sqlsql-serverreporting-servicesdiffntext
Drew Sindle
Drew Sindle

Reputation: 35

SQL converting NTEXT -> VARCHAR -> FLOAT and doing a difference

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 dividing 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.

Upvotes: 0

Views: 1709

Answers (1)

Zohar Peled
Zohar Peled

Reputation: 82474

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 ...

Upvotes: 1

Related Questions