CASE, CONVERT NULL to 'Unknown' in a column with a datatype of int.

Question

CASE, CONVERT NULL to 'Unknown' in a column with a datatype of int.

Duran

SSChampion

Points: 13259
More actions
May 2, 2017 at 12:56 am

#337695

Hello,
I have a column, and the data type is int, but there are some NULL's in there, and as I am reporting off this, I do not want to have NULL appear in the report, I'd prefer 'Unknown', and would like to change this at the database level. The code I am trying is...
CASE ISNULL(TableName.[Days since account logon],'NULL')
     WHEN 'NULL' THEN TRY_CONVERT(varchar(10),TableName.[Days since account logon],'Unknown')
     --WHEN 'NULL' THEN 'Unknown'
     ELSE [Days since account logon]
     END [Last Logon]
...I'm just not getting it. Would love some help, I expect this is easy for a lot of people. The error I receive is..
Argument data type varchar is invalid for argument 3 of convert function.
..I understand what it is saying, I just cant work out the workaround, or what the code should be. Thank you for reading.
Regards,
D.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99126 More actions · Answer 1

I would really recommend against converting your integer value to a varchar. If you need to display NULL values as a different value, then I would suggest you do this in your presentation layer, as then you preserve the data type. This means things like your aggregate functions will continue to work (among other things). What are you using to display your results?

As a simple example, does this SQL give the result you would expect?
SELECT MAX(DaysSinceLogin) AS MaxDaysSinceLogin FROM (VALUES ('1'),('2'),('Unknown')) T(DaysSinceLogin);

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

Thom is absolutely right. Having your reporting requirement dictate your database structure is like the tail wagging the dog. Keep your database in proper normal form, and handle the pretty report formats in the presentation layer.

John

Mat Richardson SSC-Addicted Points: 401 More actions · Answer 3

You could try something like this:-

select coalesce(convert(varchar,[Days Since Account Logon]),'Unknown') as [Days Since Account Logon]

But I agree with the rest, you shouldn't be doing this kind of thing at the database level.

Luis Cazares SSC Guru Points: 183698 More actions · Answer 4

m.richardson.home - Tuesday, May 2, 2017 3:41 AM
You could try something like this:-
select coalesce(convert(varchar(10),[Days Since Account Logon]),'Unknown') as [Days Since Account Logon]
But I agree with the rest, you shouldn't be doing this kind of thing at the database level.

There, fixed that for you. 😉

P.S. Always define the length of your strings.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Duran SSChampion Points: 13259 More actions · Answer 5

Hi Everyone,

Thank you for your replies, yes, having read your responses, I'd say doing it at the presentation level does indeed make total sense. I think I was a bit tunnel visioned at the end of a long day!

Thanks, Guys.

Kind Regards,
D.