>>Hi,
>>
>>The following is a segment of one of SQL Selects:
>>
>>

>>sum( CASE WHEN DATEDIFF(dd, MYTABLE.DATE1, MYTABLE.DATE2) <= 0 THEN 1 ELSE 0 END) as DONE_ONTIME 
>>

>>
>>Basically the above counts how many jobs were done on time. If DATE2 is greater than DATE1 the result should be 1 (done on time).
>>
>>What I find is that even if some of the records/rows have the value in field DATE2 set to NULL, the resulting value of DONE_ONTIME is always 0 (zero).
>>I would like to account for these records in such way that when DATE2 is NULL the DATEDIFF() would see it as very early date. That is, in case of DATE2 NULL the DATEDIFF() return 0. But for other records, it should be whatever the difference.
>>
>>How do you "deal" with the nulls in the above? TIA
>>
>>UPDATE. I think I am going to create a CASE within CASE to check for null value and if it is, set the DATE2 to '19000101'.
>
>Why should you set to 19000101??? It is a valid and old date, NULL already means that it is not filled.
>
>

>sum( CASE WHEN DATE2 is not null AND DATEDIFF(dd, MYTABLE.DATE1, MYTABLE.DATE2) <= 0 THEN 1 ELSE 0 END) as DONE_ONTIME 
>

>
>HTH
>
>Cetin
>
>PS: Sorry for the message. I forgot how to use UT and didn't notice that the question was old and had replies already.

Hi Cetin,

Setting to some old date (e.g. 19000101) was just a way for me to get around the "issue" with a NULL date. I believe your expression comparing to NULL will work as well too.
Thank you. And I am glad you are back on UT and hopefully completely recovered. Happy and Healthy New Year!