Filter recordset by date interval?

[charmac2013]

I have a php page that displays "orders" from a mySql database. Each order has a field named "weekof" which represents the week during which it will be processed. The value for this field is the date of the monday of that week in m/d/Y format. I want to filter the record set so that when the date() is within the interval of moday to sunday all the records for that week will be displayed. Any idea of where to go or how to possibly do this is greatly appreciated. If I didn't explain the problem well enough please let me know and I will try to clear up whatever I can. Thanks again...

[bregent · Accepted solution]

Try:

Select * from MyTable where DATE_FORMAT(weekof, '%x %v') = Date_FORMAT (CURDATE(), '%x %v')

[charmac2013]

That worked great thanks so much for the help.

[charmac2013]

Thanks again for that function, I just had one question about it. I am dispalying 3 separate weeks on this page the first is the current week so I used the exact code you posted... The 2nd is "next" week so I did the following:

where DATE_FORMAT(weekof, '%x %v') = Date_FORMAT (CURDATE()+7, '%x %v')

That worked great as well. The 3rd week is "two weeks from now" so I did the following...

where DATE_FORMAT(weekof, '%x %v') = Date_FORMAT (CURDATE()+14, '%x %v')

For some reason this one isn't wokring... If I set it back a week to just "+7" then it displayes "next week" as expected however when 14 days are added it shows no results for some reason... Do I need to format something different to go 2 weeks into the future?

Thanks for all your help.

[bregent]

>CURDATE()+14

To be honest, I'm not sure what value is produced when adding a value to a date in that manner. If you want to do date math, use one of the date math functions. Use ADDDATE() or DATEADD().

[charmac2013]

Thanks again... Used a DATE_ADD() funtion inside the DATE_FORMAT() function and it works perfectly now.