# D, IS Null

• Guys,

I am adapting the below code and i understand what it is doing with the exception of the d, ISNULL.  Can someone help explain what this is doing?

SUM(CASE WHEN DATEDIFF(d, ISNULL(c.Folldate, '01-jan-1900'), Getdate()) BETWEEN 0 AND 7 THEN 1 ELSE 0 END) AS [0-7 Days old]

• d and ISNULL(c.Folldate, '01-jan-1900') are the first two arguments of the DATEDIFF function.  d means days, and ISNULL(c.Folldate, '01-jan-1900') means c.Folldate unless it's NULL, in which case 1st January 1900.  So here, the DATEDIFF function finds the number of days between c.Folldate and today, or between 1st January 1900 and today if c.Folldate is NULL.

John

• ISNULL returns the value of the second parameter if the first parameter has the value NULL. So, in the case, if the value of c.Folldate is NULL, then the value '01-Jan-1900' will be used instead.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

• Thom A - Wednesday, November 1, 2017 10:27 AM

ISNULL returns the value of the second parameter if the first parameter has the value NULL. So, in the case, if the value of c.Folldate is NULL, then the value '01-Jan-1900' will be used instead.

The truly sad part is that the ISNULL is completely unnecessary, which makes this harder to read.   The overall purpose of the query is to COUNT how many records have a FollDate value within the last 7 days.   The isnull is attempting to account for NULLs and not count them:  this would have happened by default even without going through and adding the ISNULL.

Edit:

Craig - pretty sure you just need this:

`SUM(               CASE                          WHEN DATEDIFF(day,c.Folldate, Getdate()) BETWEEN 0 AND 7                           THEN 1                            ELSE 0                END        ) AS [0-7 Days old]                                   `

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Viewing 4 posts - 1 through 3 (of 3 total)