* Jack Lauman
> I have a table with a column "date" that contains dates in SQL format,
> i.e. 2003-08-10
>
> I need to query the table and return all the rows from the current date
> thru the next 6 days. (i.e. today 2003-08-10 thru Saturday 2003-08-16).
>
> I have tried the following query which returns all of the desired rows
> except those for the current date. How can I correct this?
>
> SELECT date, time, am_pm, tz, height, cond FROM cherry_point_tides WHERE
> TO_DAYS(date) - TO_DAYS(NOW()) <= 6 and date > NOW();
You could use TO_DAYS, and greater-than-or-equal (>=):
SELECT date, time, am_pm, tz, height, cond
FROM cherry_point_tides
WHERE
TO_DAYS(date) - TO_DAYS(NOW()) <= 6 AND
TO_DAYS(date) >= TO_DAYS(NOW());
You could also have used the DATE_FORMAT() function...
SELECT date, time, am_pm, tz, height, cond
FROM cherry_point_tides
WHERE
TO_DAYS(date) - TO_DAYS(NOW()) <= 6 AND
date >= DATE_FORMAT(NOW(),"%Y-%m-%d");
... but you probably should use CURDATE(), which returns a date only,
without the time of day:
SELECT date, time, am_pm, tz, height, cond
FROM cherry_point_tides
WHERE
TO_DAYS(date) - TO_DAYS(CURDATE()) <= 6 AND
date >= CURDATE();
<URL: http://www.mysql.com/doc/en/Date_and_time_functions.html >
The BETWEEN operator can make this cleaner and easier to read:
SELECT date, time, am_pm, tz, height, cond
FROM cherry_point_tides
WHERE
TO_DAYS(date) BETWEEN TO_DAYS(CURDATE()) AND TO_DAYS(CURDATE()) + 6
<URL: http://www.mysql.com/doc/en/Comparison_Operators.html >
...and finally, if you need an index on the 'date' column, you can't run it
thru a function:
SELECT date, time, am_pm, tz, height, cond
FROM cherry_point_tides
WHERE
date BETWEEN CURDATE() AND CURDATE() + INTERVAL 6 DAY
This use of INTERVAL was introduced in version 3.23.4:
<URL: http://www.mysql.com/doc/en/News-3.23.4.html >
The interval types are documented under DATE_ADD() and DATE_SUB():
<URL: http://www.mysql.com/doc/en/Date_and_time_functions.html >
HTH,
--
Roger
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
