asked
CASE WHEN issue with datatypes other than string
Hi, I need to create a report displaying string(varchar), int and datetime when i need to display blank in string, i put ' ' and it works, but same thing dont work for int and datetime values when i want to display blank, it displays 0 for int fields and '1900-01-01 00:00:00.000' for datetime field but I want notting to be displayed (so that the report looks neat) eg. SELECT .......... .......... ,CASE --this returns INT value WHEN temperature BETWEEN 1 AND 32 THEN r.tempC ELSE '' END as [Temp] ,CASE --this returns DATETIME value WHEN temperature BETWEEN 1 AND 32 THEN r.recordedDATE ELSE '' END as [Recorded Date] ............. ............. Help appreciated :) regards, sqlhungry
I suggest you return NULL value instead of empty string as when the select statement is processed, then if first of the records returned is an int or datetime result, then an int or datetime respectively is chosen as data type for that column and '' (empty string) cannot be converted to a valid integer or datatime value. If you use NULL, then nothing will be displayed for that value or you can process the NULL as you require in the report. SELECT .......... .......... ,CASE --this returns INT value WHEN temperature BETWEEN 1 AND 32 THEN r.tempC ELSE NULL END as [Temp] ,CASE --this returns DATETIME value WHEN temperature BETWEEN 1 AND 32 THEN r.recordedDATE ELSE NULL END as [Recorded Date] ............. .............
You could of course return the int value converted to a string, then it's possible to return blank string in the ELSE-part. That's if you really can't stand NULL values being returned. SELECT .......... .......... ,CASE --this now returns varchar value WHEN temperature BETWEEN 1 AND 32 THEN CAST(r.tempC AS varchar(10)) ELSE '' END as [Temp] ,CASE --this now returns varchar value WHEN temperature BETWEEN 1 AND 32 THEN CAST(r.recordedDATE AS varchar(24)) ELSE '' END as [Recorded Date] ............. .............
I'm pretty sure the result will be used in calculations in the presentation layer (Order, Sum operations etc) so I wouldn't want to use my own solution. Not in an ideal world. But there are often other factors. Like having to tell someone else to make changes in a part of a project that that someone won't ever prioritize. But basically this is my point of view: If a value must be casted, do it as late as possible, otherwise there's great risk it will be casted back and forth, which is not only resource consuming - it also increases the risk for errors. Date conversion is more or less a science on its own. Add the different regional notations for decimals and thousand-separators in numbers. No. When I think about it; I wouldn't use the solution I suggested - I would chase the presentation-team and have them alter the reports to handle NULL values correctly.
Classic application v/s database engine presentation issue. Where do you want it handled?
If I had the opportunity to decide how SQL server should work, I would have removed the possibility to do implicit conversions. Too many lazy developers depend on the implicit conversions instead of explicit conversions, and that causes some terrible performance issues. SQL server shouldn't have anything with the presentation of the data, and this is a classic presentation issue.
