SQL: ISNULL function with different type of parameters

Question

In SQL Server, ISNULL() function has to same type of parameters.

check_expression

Is the expression to be checked for NULL. check_expression can be of any type.

replacement_value

Is the expression to be returned if check_expression is NULL. replacement_value must have the same type as check_expresssion.

How can I use it with different type of parameter? I want to use with date and string parameter like this ISNULL(A.DATE, '-')

NOTE: Type of A.DATE is datetime.

EDIT-1: My full query which is getting 0 row:

    SELECT A.pkey as KREDİ, A.SUMMARY , D.BayiStatu AS STATU, D.Sorumlu AS SORUMLU, C.BayiSonuc as SONUC, ISNULL( CONVERT(VARCHAR(25), A.CREATED, 112) , '-' ), ISNULL( CONVERT(VARCHAR(25), A.RESOLUTIONDATE, 112) , '-' ), dbo.CUSTVAL(11931, A.ID, 'S') AS BAYİ, ISNULL( CONVERT(VARCHAR(25), dbo.GetLastStatuTime(A.ID), 112) , '-' ) AS SON_STATU_TAR,j2.SUMMARY, ISNULL( CONVERT(VARCHAR(25), j2.CREATED, 112) , '-' ), ISNULL( CONVERT(VARCHAR(25), j2.RESOLUTIONDATE, 112) , '-' ), j3.SUMMARY, ISNULL( CONVERT(VARCHAR(25), j3.CREATED, 112) , '-' ), ISNULL( CONVERT(VARCHAR(25), j3.RESOLUTIONDATE, 112) , '-' )
FROM AspNetServicesDB.dbo.SONUC_MAP C, JİRA.resolution E, jira.issuestatus B, AspNetServicesDB.dbo.STATU_MAP D, Jira.jiraissue A
INNER JOIN Jira.issuelink i
    ON i.SOURCE = A.ID and i.SEQUENCE = 0
INNER JOIN Jira.jiraissue As j2
    ON i.DESTINATION =j2.ID 
LEFT JOIN Jira.issuelink i2 
    ON i2.SOURCE = A.ID  and i2.SEQUENCE = 1
LEFT JOIN Jira.jiraissue As j3 
    ON i2.DESTINATION = j3.ID
WHERE A.issuestatus = B.ID
AND 'BAŞARAN OTOMATİV' = dbo.CUSTVAL(11931, A.ID, 'S')
AND B.pname = D.JiraStatu collate Turkish_CS_AI
AND A.issuetype != 11
AND A.RESOLUTION = E.ID
AND E.pname = C.JiraSonuc collate Turkish_CS_AI

EDIT-2: But this working

select ISNULL( CONVERT(VARCHAR(25), A.RESOLUTIONDATE, 112) , '-' )
FROM Jira.jiraissue A

Could be the reason JOIN ?

If select ISNULL( CONVERT(VARCHAR(25), A.RESOLUTIONDATE, 112) , '-' ) works then this ISNULL(A.DATE, '-') problem is solved. Now, you have another problem. This means another question. — Bogdan Sahlean
– Bogdan Sahlean, Commented Nov 1, 2011 at 9:39

GolezTrol · Accepted Answer · 2011-11-01 08:15:02Z

4

You can't. The ISNULL function is used by itself as a query result column, or in an expression that eventually is a column in the query result. All fields/rows in a column must have the same data type. So you'll have to choose.

One solution would be to cast the DATE to string, so the result is always a string, but i feel the best solution would be to return NULL for empty dates and let the presentation layer decide whether or not the NULL dates should be shown as - and in what format the non-null dates should be displayed (client locale settings).

With presentation layer, I mean anything that displays or outputs this data, which can be a web page, a CSV exporter, a reporting tool, whatever.

edited Nov 1, 2011 at 8:15

answered Nov 1, 2011 at 7:43

GolezTrol

116k19 gold badges186 silver badges215 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Soner Gönül Over a year ago

I use in Gridview and It's dateformatstring is {0:f}. How can I do in with Gridview for checking is null or not?

GolezTrol Over a year ago

I'm not much of a .NET guy, but I think you should be able to specify a column formatting, or maybe even an even to do the formatting somewhere. Can't be sure though, never done anything with this GridView, but I would be surprised if this wasn't easy to do.

onedaywhen Over a year ago

Did you mean to say "IFNULL" when the question is about ISNULL()?

GolezTrol Over a year ago

Changed it. I meant that one function that replaces a NULL value with another value, which is called IFNULL, ISNULL, NVL or maybe other names, depending on the database. ;)

GolezTrol Over a year ago

Yes, and NVL is Oracle. But they are essentially the same function. I just made a typo.

|

Code Magician · Accepted Answer · 2011-11-01 07:44:18Z

4

Can you simply cast your date as a varchar? Since you're output may not be a valid date anyway, this should work just fine:

ISNULL(CONVERT(varchar(10), A.Date, 101),'-')

answered Nov 1, 2011 at 7:44

Code Magician

24.1k7 gold badges63 silver badges77 bronze badges

2 Comments

Andriy M Over a year ago

@SonerGönül: Are you using this expression as a filter (somewhere in WHERE or ON)?

Soner Gönül Over a year ago

@AndriyM I added full query in my question.

Mikael Eriksson · Accepted Answer · 2011-11-01 07:44:48Z

3

You need to cast the date to a string if you really want a - instead of null.

declare @T table(adate datetime)
insert into @T values(null)
insert into @T values(getdate())

select isnull(convert(varchar(23), adate, 126), '-')
from @T

answered Nov 1, 2011 at 7:44

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

Comments

ypercubeᵀᴹ · Accepted Answer · 2011-11-01 07:49:59Z

1

You can try:

 CASE WHEN A.DATE IS NULL
      THEN '-'
      ELSE CONVERT(VARCHAR(25), A.DATE, 112)
 END

edited Nov 1, 2011 at 7:49

answered Nov 1, 2011 at 7:44

ypercubeᵀᴹ

116k19 gold badges181 silver badges249 bronze badges

Collectives™ on Stack Overflow

SQL: ISNULL function with different type of parameters

4 Answers 4

9 Comments

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

9 Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related