Re: Find all the dates in the calendar week?

On Thursday 6 July 2000, at 9 h 28, the keyboard of Stephane Bortzmeyer
<bortzmeyer(at)pasteur(dot)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";