Jump to content


Photo

datetime comparison and NULL values


  • Please log in to reply
4 replies to this topic

#1 gogo

gogo
  • New Members
  • Pip
  • Newbie
  • 3 posts

Posted 08 August 2006 - 03:21 PM

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.

#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 08 August 2006 - 03:23 PM

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?
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 gogo

gogo
  • New Members
  • Pip
  • Newbie
  • 3 posts

Posted 08 August 2006 - 03:33 PM

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

#4 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 08 August 2006 - 09:04 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#5 gogo

gogo
  • New Members
  • Pip
  • Newbie
  • 3 posts

Posted 08 August 2006 - 11:01 PM

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.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users