select records based on date range

Anon

Hello Everyone!

I am trying to select records where the date is between todays date and 7 days from now.

I know i have to consider year, month and day but I can't figure out how to do it. I can do it based on month:

$today=date("Ymd", mktime(0,0,0, date(m), date(d),date(Y)));

$nextDate=date("Ymd", mktime(0,0,0, date(m), date(d)+1,date(Y)));

IN my SELECT Statement:
AND (month(game.GameDate) >=month($today) || month(game.GameDate) < month($nextDate))

in my database GameDate is in the format of 2001-01-23. Do I have to somehow strip the date of the "-" in the select statement? I don't know how that would be done though.

I think I somehow have to say
"SELECT *
FROM ITGame
WHERE ....
AND (GameDate>=$todayDate && GameDate<=$dateInSevenDays)";

Please help...
Thanks for your time. Any help is greatly appreciated!

Anon

Hello,

Try this:

SELECT * FROM ITGame WHERE .... AND DAYOFMONTH(GameDate) >= $todayDate AND DAYOFMONTH(GameDate) < $nextDate

Hope that works. Let me know

Bill

Anon

no it didn't work. could that be because my GameDate is formatted 2001-01-24? IF so how can I change that?

Anon

nevermind it worked! Thanks I figured it out.

Anon

Sorry I thought it was working and there were just no records in that range, but it's not true. The records should have shown up

amcgrath

have you tried something like select * from table where date_fiel between $low_date and $high_date....

mithril

SELECT * FROM ITGame WHERE GameDate BETWEEN CURDATE() AND DATE_ADD(CURDATE(),INTERVAL 7 DAY);

Check out the Date and Time Functions section of the MySQL Manual if you have further questions.

http://www.mysql.com/doc/D/a/Date_and_time_functions.html

-geoff

Anon

Note:This is for dates which has differenece of any number of days between them as selected by the user.
As asked by you to have days difference as seven you have to change the coding explicitly.
This wont work for days which dont have seven as difference.
Why dont you capture those from a HTML FORM which can work for any number of days gap.
Suppose in somefile called 'date.html' choose the FROM and TO dates.
Action for this 'date.html' is 'date.php'
In 'date.php' append the values with '-' in the format year-month-day as:
$fromdate="$fy"."-"."$fm"."-"."$fd";
$todate="$ty"."-"."$tm"."-"."$td";
and your query will be
$query = "select from tablename where fieldname between '$fromdate' and '$todate'";
--Ajay