datetime comparison and NULL values

[gogo]

I have the following query:

  SELECT COUNT(fldVolunteerID)
  FROM tbl_volunteer
  WHERE fldDateDenied IS NOT NULL
  AND UNIX_TIMESTAMP(fldDateDenied) > UNIX_TIMESTAMP(fldDateApproved)
  AND fldDenyFlag = 1

and it is not returning the desired output. Problem is that both fldDateDenied and fldDateApproved potentially could be NULL, and I think that is stuffing up my query.

What will the effect be of converting a NULL value to UNIX_TIMSTAMP, and then performing a comparison? In the above example: what if fldDateApproved = NULL ?

Currently, the query results in a COUNT of 0 (zero), which is not correct (there should be results).

All help much appreciated.

[fenway]

Not sure... you'd probably get NULL or 0 back, both of which would interfere with your query.  You'll have to handle these boundary cases separately with ORs.  Also, why the converstion -- what types of these columns anyway?

[gogo]

Columntype = datetime (default NULL). Yeah, wasn't sure if I needed the conversion. It was just my way of trying to deal w/ the NULL issue.

What do you mean by ORs?

Forgot to mention: running MySQL 4.0.27

[fenway]

You don't need the conversion... but you do need to check to see whether the values are NULL or not, and write your where clause accordingly.

[gogo]

Duh! ORs... After rereading your post this morning, I finally got it. Must have been very tired. Was thinking it was an acronym of sorts.

Anyways, 30 secs after reading your reply I got it working:

SELECT COUNT(fldVolunteerID)
FROM tbl_volunteer
WHERE fldDateDenied IS NOT NULL
AND fldDateApproved IS NOT NULL
AND fldDateDenied > fldDateApproved
AND fldDenyFlag = 1
OR  fldDateDenied IS NOT NULL
AND fldDateApproved IS NULL
AND fldDenyFlag = 1

Thanks heaps fenway. Much obliged.