Page 1 of 1

Help with a Query statement of records with NULLs

Posted: Tue Aug 05, 2003 12:30 pm
by Swede78
I'm having a problem writing a query that formats a date. The problem is that not all my records include a date. Sometimes the date field will be NULL. So, when it tries to select the data from a record with a NULL date, the query results in 0 records. How can I write this query, so that it will give me a result even if the date field is NULL?

Here's my current query code:

Code: Select all

<?php
$query = "SELECT *, DATE_FORMAT(DateField, '%M %D, %Y') as DateField FROM table";
?>
Note: my date field is a MySQL date field (YYYY-MM-DD). Using PHP 4.3 and MySQL 3.23 on a Win2K/IIS machine.

I have a feeling that someone is going to say, "use a timestamp for your database instead of a date field." Well, that would probably work good, but I already have hundreds of records that have the date field format. Plus, I'd like to figure this out, to see if it can be done.

Any help would be appreciated!!!

MySQL and NULL Values

Posted: Wed Aug 06, 2003 12:32 am
by rterrar
First I would like to point out that in MySQL NULL is not the same as an empty string ("") and anytime you use NULL as the right had operator (ex: datefield = NULL) it will return an empty set because NULL always returns false. So in your case I would use something like this:
...WHERE (datefield IS NULL) OR (datefield IS NOT NULL)

You can also look up these two functions in the MySQL docs and see if they would better serve your purpose COALESCE and IFNULL.
I'm thinking that COALESCE would serve your purpose, It lets you set NULL values to anything you want in the return, (not the table) and then you could test for that value and you would know that it is NULL in the actual table. I've used this to treat the NULL as 0. COALESCE(datefield,0)

With out actually testing this:))) I would maybe do something like:
SELECT * , DATE_FORMAT(COALESCE(datefield,0),'%M %D, %Y') as datefield FROM table

Posted: Wed Aug 06, 2003 9:38 am
by Swede78
Thanks rterrar,

I'll give your code a try and I'll look into this COALESCE and IFNULL, at mysql.com. I appreciate the reply!