Jump to content

Archived

This topic is now archived and is closed to further replies.

gogo

datetime comparison and NULL values

Recommended Posts

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.

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.