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 开发者_如何学JAVAthe reason?

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

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

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