Case Statement - Convert Varchar to Number

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

General SQL Server Forums

New to SQL Server Programming

Case Statement - Convert Varchar to Number

Author

Topic

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2009-05-08 : 10:31:36

I am having problems with the case statement. I have a field that is varchar but it needs to be numeric.

This is what I have, I actually don't want decimal though I want it to be numeric.....

WeektwoMonday =
Case When convert(varchar,cast(t3.WeektwoMonday as Decimal(10,2)) = 'Holiday(H)' Then '8'
Else '0'
End,

This is the error Incorrect syntax near '='.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-05-08 : 10:35:40

there is one ) missing

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-08 : 10:45:56

your query doesnt make sense. how will a decimal field have 'Holiday(H)' value stored in it?

convert(varchar,cast(t3.WeektwoMonday as Decimal(10,2)) = 'Holiday(H)'

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2009-05-08 : 10:50:06

The field t3.weektwoMonday says "Holiday", we need to create a case statment that says

t3.weektwoMonday =
Case when t3.weektwoMonday = 'Holiday(H)' Then '8'
Else '0'
End,

What the problem is I can't add up this field because it is a varchar field. I need it to be a numberic so it counts.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-08 : 10:57:30

quote:
Originally posted by werhardt

The field t3.weektwoMonday says "Holiday", we need to create a case statment that says

t3.weektwoMonday =
Case when t3.weektwoMonday = 'Holiday(H)' Then '8'
Else '0'
End,

What the problem is I can't add up this field because it is a varchar field. I need it to be a numberic so it counts.

ok...why wre you trying to cast it to decimal? that confused me. if you want numeric then just return it as int rather than varchar that you're doing currently by removing the ' characters


t3.weektwoMonday = 
Case when t3.weektwoMonday = 'Holiday(H)' Then 8
Else 0
End

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2009-05-08 : 11:33:43

Well this does work as my case statement

t3.weektwoMonday =
Case when t3.weektwoMonday = 'Holiday(H)' Then 8
Else 0
End

But I am trying to create a store procedure that would sum up the numbers, but it won't let me. This is what I have as my sum right now.

(cast(WeekOneMonday + WeekOneTuesday+WeekOneWednesday +WeekOneThursday+WeekOneFriday+WeektwoMonday+WeektwoTuesday+
WeektwoWednesday+WeektwoThursday+WeektwoFriday as float) as [Holiday Hours])

But I got an error that says.....Incorrect syntax near the keyword 'as'.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-05-08 : 11:45:28

Can you give us a better explanation? Maybe some DDL, sample data and expected output?

You've talked about wanting your varchar field to be numeric, but it holds values like "holiday" which, obviously, is not numeric. Then you are concatenating a bunch of, presumably, varcahr fields and trying to cast that concatenation to a float...?? At this point I'm completly confused as to what you want to do.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-08 : 12:42:55

quote:
Originally posted by werhardt

Well this does work as my case statement

t3.weektwoMonday =
Case when t3.weektwoMonday = 'Holiday(H)' Then 8
Else 0
End

But I am trying to create a store procedure that would sum up the numbers, but it won't let me. This is what I have as my sum right now.

(cast(WeekOneMonday + WeekOneTuesday+WeekOneWednesday +WeekOneThursday+WeekOneFriday+WeektwoMonday+WeektwoTuesday+
WeektwoWednesday+WeektwoThursday+WeektwoFriday as float) as [Holiday Hours])

But I got an error that says.....Incorrect syntax near the keyword 'as'.

can you post full line atleast? what are weekonemonday,... fields? are they directly coming from table?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources