dealing with empty date strings

[Lossed]

Have a query that pulls out the closest delivery date to today’s date, from
two columns in a table. These columns are airdate and seadate and are the
date type with a default as ‘’. usually, for each record, there will be only
one of the columns with an actual date,the other set to the ‘’ default. It’s
this default that I’m not sure how to deal with. The SQL to get the closest
date is pretty crazy but it works and the best anyone could come up with :).
The DB is MySQL4.1. Here’s the SQL:

select colourid, adddate(curdate(), min(datediff(airdate,curdate()))) as
airdate, adddate(curdate(), min(datediff(seadate,curdate()))) as seadate

from purchase as p, stock as s

where s.modelid=<cfqueryparam value="#url.ModelID#">

and p.pending=1

and s.pid=p.pid

and (datediff(airdate,curdate())) >=0

and (datediff(seadate,curdate())) >=0

group by colourid

It works fine if both the seadate and airdate have normal date values in
them, but it returns no records if either of these colums has the default
date of ‘’. How do I deal with that?


--
Cheers,
Lossed
__when the only tool you have is a hammer, everything looks like a nail __

[paross1]

What do you want to happen with these lines if airdate and/or seadate is NULL?

and (datediff(airdate,curdate())) >=0
and (datediff(seadate,curdate())) >=0

In other words, do you want them to be ignored, evaluate as true, or evaluate
as false if the date column is NULL?

I'm not a MySQL user, but I believe that the sample below will behave as a
"don't care" condition if the date column is NULL, so that it will evaluate as
TRUE if it is NULL. In other words, if airdate or seadate is NULL, then it is
replaced by curdate(), and since the difference between curdate() and curdate()
is 0, the statement will be TRUE if the column is NULL, and it will behave as
if the line was not included in the query.

and (datediff(IFNULL(airdate,curdate()),curdate())) >=0
and (datediff(IFNULL(seadate,curdate()),curdate())) >=0

Phil

[Lossed]

tks :)

"paross1" <webforumsuser@macromedia.com> wrote in message
news:d3ebre$bgm$1@forums.macromedia.com...

> What do you want to happen with these lines if airdate and/or seadate is
> NULL?
>
> and (datediff(airdate,curdate())) >=0
> and (datediff(seadate,curdate())) >=0
>
> In other words, do you want them to be ignored, evaluate as true, or
> evaluate
> as false if the date column is NULL?
>
> I'm not a MySQL user, but I believe that the sample below will behave as
> a
> "don't care" condition if the date column is NULL, so that it will
> evaluate as
> TRUE if it is NULL. In other words, if airdate or seadate is NULL, then it
> is
> replaced by curdate(), and since the difference between curdate() and
> curdate()
> is 0, the statement will be TRUE if the column is NULL, and it will behave
> as
> if the line was not included in the query.
>
> and (datediff(IFNULL(airdate,curdate()),curdate())) >=0
> and (datediff(IFNULL(seadate,curdate()),curdate())) >=0
>
> Phil
>
>