CODEWITHSUNDEEP
sql-serverdateisnull
Ish Tech
Ish Tech

Reputation: 239

ISNULL behaviour in sql server

As per my understanding IsNull Function checks the first value if its null or blank then it returns the next value.

SELECT ISNULL(1,getdate())

but the above statement is giving error. Can any one help to highlight the reason?

Upvotes: 2

Views: 1938

Answers (3)

SQLMenace
SQLMenace

Reputation: 135011

Implicit conversion from data type datetime to int is not allowed, make the first value a char

SELECT ISNULL('1',getdate())

BTW, just be aware that ISNULL is not ANSI is proprietery and only accepts 2 parameters, COALESCE accepts a lot more

DECLARE @1 INT,@2 int, @3 INT, @4 int
SELECT @4 = 6

SELECT COALESCE(@1,@2,@3,@4)

This statement below is incorrect

IsNull Function checks the first value if its null or blank then it returns the next value.

it doesn't care for blanks

run this

SELECT ISNULL('','A')    -- Blank is returned not A
SELECT ISNULL(NULL,'A')  -- A is returned because the first value is NULL

another difference between ISNULL and COALESCE is that ISNULL will return the same length as the first parameter

run this

DECLARE @c CHAR(3)

SELECT ISNULL(@c,'not available')  -- not
SELECT COALESCE(@c,'not available') --not available

Upvotes: 7

alun
alun

Reputation: 3441

You can get it to autocast doing this but I really would not recommend it. It's super unclear to someone else looking at this what it is trying to do:

SELECT case when not 1 is null then 1 else getdate() end

Upvotes: 0

Kevin Horgan
Kevin Horgan

Reputation: 1580

I guess you are trying to replace an Integer with a Date. Try this SELECT ISNULL(1,2) maybe instead.

Upvotes: 0

Related Questions