date columns availabilitycheck mysql

[helenp]

Hi,
I done an availabilitycheck that until now seems to work just perfect, except 1 problem.

For exampel in the database if I have an booking in datecolumns llegada and salida dates 25/11 and 15/12, I would like the days 25/11 and 15/12 to be marked as available.

This is the code I have:
$result = mysql_query ("SELECT llegada, salida, propiedad from bookings where ( propiedad = '$propiedad' )
AND (('$llegada' BETWEEN llegada AND salida) or ('$salida' BETWEEN llegada AND salida) or (llegada < '$llegada' AND salida > '$salida') or (llegada > '$llegada' AND salida < '$salida'))", $dbh);

In this piece of the code this is what I would like to do but donīt work:
('$llegada' BETWEEN llegada AND salida-1) or ('$salida' BETWEEN llegada-1 AND salida)

[Salsa]

Helen, I think you'll be better off formatting your dates like 11-25-2004 (instead of 25/11) and storing them in a DATE type column. That way MySQL will be able to order them properly. When using DATE type columns, MySQL also provides functions to format the dates any way you wish when you SELECT and display them.

[helenp]

I am sorry for putting the dates like that in the thread...
the dates are in columns type date, like this 2004-11-25 so that is not the problem,
the problem is only to get date registered as entry and departure to display as available in the select.
Thanks

[Salsa]

I thought you knew better than that, but that's the first thing I saw. As to your real problem, it's not going to work for you to just add or subtract from a DATE field. You'll need to use someting like TO_DAYS; e.g.:

...(TO_DAYS('$llegada') BETWEEN TO_DAYS(llegada) AND TO_DAYS(salida)-1)...

[helenp]

thanks a lot,
I done it this way:
AND (('$llegada' BETWEEN llegada AND date_sub(salida, interval +1 day))
or ('$salida' BETWEEN date_sub(llegada, interval -1 day) AND salida)

I suppose the todays will work as well