CODEWITHSUNDEEP
mysqldate
user3169979
user3169979

Reputation: 47

How to filter the current week as a range of dates

Hi I have a MySQL database which on I am setting up a table for a Study Calendar, fields are as follows:

SELECT
studycalendarpk,
studytopic,
`module`,
startdate,
enddate,
syllabusoutline
FROM studycalendar

What I am trying to do is to create a query so that for a dashboard php page it has a query that dispays the current weeks study programme. Can someone please tell me how to setup query to filter it so that it is selected if the current date is between the startdate and enddate, thank you

Upvotes: 2

Views: 2010

Answers (3)

O. Jones
O. Jones

Reputation: 108641

You have a startdate and an enddate for each row in your table, and if I understand your requirement correctly, you want to display all rows that meet these criteria.

WHERE enddate >= start of week
  AND startdate < start of next week

You already have startdate and enddate in your table. This answer assumes that each row's enddate is constrained to be greater than or equal to the starttdate. If it isn't you'll get strange results.

You need a MySQL expression to compute the first day of the present week. Here's how you do that.

FROM_DAYS(TO_DAYS(CURDATE()) -MOD(TO_DAYS(CURDATE()) -1, 7))

This expression yields the Sunday immediately preceding CURDATE(). If your weeks are considered to start on Monday, use this instead (notice the -2).

FROM_DAYS(TO_DAYS(CURDATE()) -MOD(TO_DAYS(CURDATE()) -2, 7))

These are very useful expressions because they yield actual dates. Those dates can then be manipulated by date arithmetic such as somedate + INTERVAL 7 DAY which conveniently gives you the date a week later. This sort of arithmetic even works for the last week, and the first week, of a calendar year.

Putting it all together, here's what you do to select the records you want.

WHERE enddate  >= FROM_DAYS(TO_DAYS(CURDATE())-MOD(TO_DAYS(CURDATE())-1,7))
  AND startdate < FROM_DAYS(TO_DAYS(CURDATE())-MOD(TO_DAYS(CURDATE())-1,7))
                   + INTERVAL 7 DAY

This will get the records from your table relevant to the current week.

Upvotes: 5

Punitha Subramani
Punitha Subramani

Reputation: 1477

Can you try it? We can gett week no of use by this week() method

SELECT
`studycalendarpk`,
`studytopic`,
`module`,
`startdate`,
`enddate`,
 CAST(week(now()) AS UNSIGNED)
syllabusoutline
FROM studycalendar WHERE CAST(week(now()) AS UNSIGNED) between CAST(week('2014-09-01') AS UNSIGNED) and CAST(week('2014-09-07') AS UNSIGNED)

Upvotes: 0

juergen d
juergen d

Reputation: 204746

SELECT *
FROM studycalendar
where curdate() between startdate and enddate

Upvotes: 3

Related Questions