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.