Re: Find all the dates in the calendar week?

On Thursday 6 July 2000, at 9 h 28, the keyboard of Stephane Bortzmeyer
<bortzmeyer@pasteur.fr> wrote:

> I have a table of events with a column which stores datetimes. I want to check
> if a datetime is inside the current calendar week (i.e. from the previous
> monday to the next sunday). The purpose is to SELECT all the events of the
> week.
...
> [If there is no SQL way, I'll hack it in a Perl script. Code or suggestions
> welcome.]

Here is the code:

    my (@fields) = localtime(time());
    my ($week_day) = $fields[6];
    $week_day = ($week_day-1) % 7;
    my ($previous_monday_offset) = $week_day;
    my ($next_monday_offset) = 7 - $week_day;
    my ($begin_this_week) = &string2time (($fields[5]+1900) . " " .
                      ($fields[4]+1)  . " " .
                      ($fields[3]-
                       $previous_monday_offset) .
                      " " .
                      "0 0");
    my ($end_this_week) = &string2time (($fields[5]+1900) . " " .
                    ($fields[4]+1)  . " " .
                    ($fields[3]+
                     $next_monday_offset-1) .
                    " " .
                    "23 59");
    my ($begin_this_week_iso) = &time2iso ($begin_this_week);
    my ($end_this_week_iso) = &time2iso ($end_this_week);
...
    ($sth = $dbh->prepare( qq{
        SELECT *
        FROM conferences
        WHERE
            ((date_begin  >= '$begin_this_week_iso') AND
             (date_begin <= '$end_this_week_iso')) OR
             ((date_end  >= '$begin_this_week_iso') AND
              (date_end <= '$end_this_week_iso'))
                  ORDER BY Date_begin
                  })) or die "Can't prepare statement: $DBI::errstr";