phpmysql

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()" );
Solution

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