Function ISNULL in where clause

New Contributor

Hallo,

if I use these two where clauses 

a. where IIF(VALID_TILL IS NULL, GETDATE(), VALID_TILL) = GETDATE()

b. where ISNULL(VALID_TILL, GETDATE()) = GETDATE()

only a returns the correct results.

Can someone explan this?

Thanks 

Alto

 

1 Reply

@Altio90 

Steps to reproduce

 

CREATE TABLE #TESTTABLE (VALID_FROM DATE, VALID_TILL DATE)
INSERT INTO #TESTTABLE (VALID_FROM, VALID_TILL) VALUES (GETDATE(), GETDATE())
INSERT INTO #TESTTABLE (VALID_FROM, VALID_TILL) VALUES (GETDATE(), NULL)

 

SELECT * FROM #TESTTABLE WHERE ISNULL(VALID_TILL, GETDATE()) = CAST(GETDATE() AS DATE)
SELECT * FROM #TESTTABLE WHERE VALID_TILL = CAST(GETDATE() AS DATE)
SELECT * FROM #TESTTABLE WHERE IIF(VALID_TILL IS NULL, GETDATE(), VALID_TILL) = CAST(GETDATE() AS DATE)