Converting varchar value to a data type int

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Converting varchar value to a data type int

Author

Topic

Gary Costigan
Yak Posting Veteran

95 Posts

Posted - 2005-03-29 : 12:31:27

I'm performing a query where I subtract the value of one column from another.

SELECT losttime.CTwentyOne.SickHours_Jan_01 as 'SK BAL BEGIN',
losttime.CTwentyOne.SickHours_Current as 'SK BAL END',
losttime.CTwentyOne.SickHours_Jan_01 - losttime.CTwentyOne.SickHours_Current as 'HoursUsed'
FROM losttime.CTwentyOne

When I try to add a CASE statement to group the 'HoursUsed' I get an error: Syntax error converting the varchar value 'HoursUsed' to a column of data type int.

Here is the query.

SELECT losttime.CTwentyOne.SickHours_Jan_01 as 'SK BAL BEGIN',
losttime.CTwentyOne.SickHours_Current as 'SK BAL END',
losttime.CTwentyOne.SickHours_Jan_01 - losttime.CTwentyOne.SickHours_Current as 'HoursUsed',
CASE
WHEN 'HoursUsed' >= 1201 THEN '1200++'
WHEN 'HoursUsed' >= 1101 and 'HoursUsed' <= 1200 THEN '1101 - 1200'
WHEN 'HoursUsed' >= 1001 and 'HoursUsed' <= 1100 THEN '1001 - 1100'
WHEN 'HoursUsed' >= 901 and 'HoursUsed' <= 1000 THEN '0901 - 1000'
WHEN 'HoursUsed' >= 801 and 'HoursUsed' <= 900 THEN '0801 - 0900'
WHEN 'HoursUsed' >= 701 and 'HoursUsed' <= 800 THEN '0701 - 0800'
WHEN 'HoursUsed' >= 601 and 'HoursUsed' <= 700 THEN '0601 - 0700'
WHEN 'HoursUsed' >= 501 and 'HoursUsed' <= 600 THEN '0501 - 0600'
WHEN 'HoursUsed' >= 401 and 'HoursUsed' <= 500 THEN '0401 - 0500'
WHEN 'HoursUsed' >= 301 and 'HoursUsed' <= 400 THEN '0301 - 0400'
WHEN 'HoursUsed' >= 201 and 'HoursUsed' <= 300 THEN '0201 - 0300'
WHEN 'HoursUsed' >= 161 and 'HoursUsed' <= 200 THEN '0161 - 0200'
WHEN 'HoursUsed' >= 121 and 'HoursUsed' <= 160 THEN '0121 - 0160'
WHEN 'HoursUsed' >= 81 and 'HoursUsed' <= 120 THEN '0081 - 0120'
WHEN 'HoursUsed' >= 41 and 'HoursUsed' <= 80 THEN '0041 - 0080'
WHEN 'HoursUsed' >= 1 and 'HoursUsed' < 41 THEN '0001 - 0040'
ELSE '000'
END AS 'HOURS USED GROUP'

FROM losttime.CTwentyOne

I've tried CONVERTING the subtraction line, but haven't had any luck.

Thanks.

GC

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-03-29 : 12:34:05

remove the single quotes from 'HoursUsed'. Adding single quotes around a column turns it into a string literal. You are literally comparing the string 'HoursUsed' to those values and not the contents of the HoursUsed column.

- Jeff

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-03-29 : 12:44:32

just noticed that HoursUsed is an expression. you need to use a derived table to refernce an expression in other parts of your select list, so you can just do this:


select BalBegin, BalEnd,
     case when HoursUsed ..... etc .... as HoursUsedGroup
from
  (select SickHours_Jan_01 as BalBegin, 
         SickHours_Current as BalEnd,
         SickHours_Jan_01 - SickHours_Current as HoursUsed 
   from 
         losttime.CTwentyOne
  ) a

some advice:

1) don't use spaces in your column names
2) don't use ' characters around your column names; it is too confusing. Use [ and ]
3) your column and table names look very troubling; make sure you are familiar with database theory and that your tables are normalized. How do you run this query for February? What does CTwentyOne represent?

- Jeff

Gary Costigan
Yak Posting Veteran

95 Posts

Posted - 2005-03-29 : 13:56:26

Jeff,

Thanks ever so much for answering my question, I've made the adjustments and it works. And, for your advise. I know my SQL structure stinks, I'm self taught and learning every day. It's people like you in being kind enough to help that makes this site so great.

Thanks again.

GC

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources