Skip to main content
C
charmac2013
Known Participant
October 15, 2011
Answered

Filter recordset by date interval?

  • October 15, 2011
  • 1 reply
  • 1075 views

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...

This topic has been closed for replies.
Correct answer bregent

Try:

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


1 reply

B
bregentCorrect answer
Participating Frequently
October 16, 2011

Try:

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


C
charmac2013Author
Known Participant
October 19, 2011

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.


C
charmac2013Author
Known Participant
October 20, 2011

>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().


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

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices