Mysql Question posted in
The official MySQL documentation can be found here.

MySQL given date between monday and sunday

I want to output all entries from the database within this calendar week based on a given date.
Example:
I have entered

2024-11-20

Check whether there are any other entries in the calendar week of this date that should be output.

I tried the following:

$wochenzieldaten = $DB->get_records_sql( "SELECT * FROM {schultimer_wochenziel}
        WHERE wzuserid = {$getuserid}
        AND datum BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 1 WEEK)
        AND CURRENT_DATE()" );
Tags:

2

Answers


  1. 0

    If you do your date calculations in PHP then you’ll get a more portable query. PHP has a very nice feature in strtotime() and new DateTime() that allows you to use flexible relative descriptions, which you can use to find the week bounding dates. In this case, if the target date is already a Monday, then that’s the start of our range. Otherwise, we want to go backwards from the target date until we find the first Monday. This can be done with previous Monday in the date string. Once we find the range start, we can calculate the range end with the +6 days relative string.

    $target = '2024-11-20';

if (date('l', strtotime($target)) === 'Monday') {
    $start = $target;
} else {
    $start = date('Y-m-d', strtotime($target . ' previous Monday'));
}

$stop = date('Y-m-d', strtotime($start . ' +6 days'));

echo "$startn";
echo "$stopn";

    This yields:

    2024-11-18
2024-11-24

    Note this will work just fine across year boundaries:

    $target = '2024-12-31';

    Yields:

    2024-12-30
2025-01-05

    Now you can use these values in your query, to find all the records that exist within that week:

    SELECT
  *
FROM
  {schultimer_wochenziel}
WHERE
  wzuserid = :userid AND
  datum >= :start AND
  datum <= :stop
    Login or Signup to reply.
  2. 0

    This might be what you’re looking for:

    SELECT * FROM {schultimer_wochenziel}
WHERE wzuserid = {$getuserid}
AND datum BETWEEN
  DATE_SUB(CURRENT_DATE(), INTERVAL WEEKDAY(CURRENT_DATE()) DAY)
AND
  DATE_ADD(CURRENT_DATE(), INTERVAL 6 - WEEKDAY(CURRENT_DATE()) DAY)

    Or replace all CURRENT_DATE() by a placeholder like :date, if the CURRENT_DATE() in your question was only for experimenting.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search