Select Date Range

[SBuzzT]

I need to select all the entries for a 7 day period based on 1 date being supplied. In other words, if 2006-01-01 is selected, all entries from (and including) 2006-01-01 to(and including) 2006-01-07 would be returned. I am using a MySQL database.

Any ideas?

 

[sleipnir214]

I'd use the given date as the loweer bound and the MySQL function adddate() (

http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html)

as the upper bound of the where clause of my SELECT query.




Want the best answers? Ask the best questions! TANSTAAFL!

 

[SBuzzT]

I played around with that, but was unable to get it to work correctly.

 

[sleipnir214]

With what are you having a problem?


Want the best answers? Ask the best questions! TANSTAAFL!

 

[SBuzzT]

No matter how I write the query, I can seem to isolate the 1 week. I either get all the result less than and equal or greater than and equal to the supplied date. In other words, I only want results from 2006-01-01 through 2006-01-07. Somehow, I manage to also get results from more than 1 week before or after as well.

 

[sleipnir214]

What does the WHERE clause in your SELECT query look like?


Want the best answers? Ask the best questions! TANSTAAFL!

 

[SBuzzT]

SELECT * from TABLE where TO_DAYS(DATE_COL) - TO_DAYS('$week')<=7

 

[sleipnir214]

You have your column and date reversed.

On my MySQL installation, this query:

SELECT * from foo where TO_DAYS('2006-04-11') - TO_DAYS(the_date)<=7

works just fine selecting the last 7 days.


Want the best answers? Ask the best questions! TANSTAAFL!

 

[SBuzzT]

Yes, but what I am trying to do is isolate THAT week. The way the query is now, it will also give results from before the selected date. The selected date should be the start of the results and find nothing exceeding 7 days later.

 

[sleipnir214]

Again, I'd use something like adddate():

select * from foo where the_date >= '2006-04-02' AND the_date <= adddate('2006-04-02', interval 7 day);




Want the best answers? Ask the best questions! TANSTAAFL!

 

[SBuzzT]

Sigh...

I gave up on the MySQL method and went with this ($week is the selected date). Works...

$week2  = date('Y-m-d', strtotime ("+1 week $week"));
$sql    = mysql_query("SELECT * from TABLE where DATE_COL between '$week' and '$week2'");