Forum Discussion

Altio90's avatar
Altio90
Copper Contributor
Nov 23, 2022

Function ISNULL in where clause

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

 

  • Altio90's avatar
    Altio90
    Copper Contributor
    Nov 24, 2022

    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)

     

Share

Resources