Convert Int to String in Case - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Convert Int to String in Case

Author

Topic

Looper
Yak Posting Veteran

68 Posts

Posted - 2011-04-05 : 10:06:49

Field Shours is a decimal field (5,2) that allows nulls. But in this query if it is a Null I want it to come out as '' empty string for user readability. But if I Change WHEN emp.PTime = 'W' THEN NULL to WHEN emp.PTime = 'W' THEN '' I get a conversion error.


CASE
WHEN emp.PTime = 'W' THEN NULL
WHEN emp.PTime = 'P' AND emp.PayFreq = 'W' THEN ISNULL(CAST(esp.SHours AS varchar),'')
WHEN emp.PTime = 'P' AND emp.PaymFreq = 'M' THEN CAST(ROUND((esp.SHours * 84) / 365,2) AS Decimal(5,2))
END AS SHours,

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-04-05 : 10:15:56

What happens when you change

WHEN emp.PTime = 'W' THEN NULL

to

WHEN emp.PTime = 'W' THEN CAST(NULL as VARHCAR(30))

Madhivanan

Failing to plan is Planning to fail

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-04-05 : 10:16:12

CASE
WHEN emp.PTime = 'W' THEN NULL
WHEN emp.PTime = 'P' AND emp.PayFreq = 'W' THEN ISNULL(CAST(esp.SHours AS varchar),'')
WHEN emp.PTime = 'P' AND emp.PaymFreq = 'M' THEN STR(ROUND((esp.SHours * 84) / 365,2),5,2)
END AS SHours,

Looper
Yak Posting Veteran

68 Posts

Posted - 2011-04-05 : 10:24:14

Neither suggestion works it still remains as NULL instead of ''

Looper
Yak Posting Veteran

68 Posts

Posted - 2011-04-05 : 10:32:54

quote:
Originally posted by robvolk

CASE
WHEN emp.PTime = 'W' THEN NULL
WHEN emp.PTime = 'P' AND emp.PayFreq = 'W' THEN ISNULL(CAST(esp.SHours AS varchar),'')
WHEN emp.PTime = 'P' AND emp.PaymFreq = 'M' THEN STR(ROUND((esp.SHours * 84) / 365,2),5,2)
END AS SHours,

sorry this does work if I change the WHEN emp.PTime = 'W' THEN NULL to WHEN emp.PTime = 'W' THEN ''

Cheers

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-04-05 : 10:33:34

Note that the condition WHEN emp.PTime = 'W' THEN NULL will also result to NULL

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources