To continue reading for free, register below or login
To read more you must become a member of SearchOracle.com
What an unusual request. Hard to imagine where a table
of Friday dates might be required. Nevertheless, ask an SQL question,
and you will get an SQL answer.
With SQL, we are concerned with sets. The set we need here
is a set of Friday dates. We're going to generate these by adding
multiples of 7 to the date of the first Friday of the year.
To do this, we'll need the numbers 0 through 51, which will
be the multiples of 7 we want to add. We can get these
by a familar technique involving a numbers table.
For example, see
The Integers table (28 January, 2004), which explains how
to generate the numbers 0 through 999 using a three-way cross join
of the integers from 0 through 9. We'll use a similar join here,
slightly simpler because we only need 52 numbers.
The other part of the problem is to determine the first Friday of
the year. Let's assign a number to the days of the week, so that
Sunday=1, Monday=2, and so on, up to Saturday=7. Obtain this weekday number
for January 1st of the year in question, subtract this number
from 13, and take the modulus with respect to 7. This gives the
number of days to add to January 1st, to get to the first Friday of
the year. Finally, to this Friday date, add multiples of 7 from
the numbers table.
Here's the solution, using MySQL syntax for illustrative purposes:
select date_add('2007-01-01'
, interval
mod(13-dayofweek('2007-01-01'),7)
+ n * 7 day ) as friday
from (
select 10*t.i + u.i as n
from integers t
cross
join integers u
) as numbers
where n between 0 and 51
order
by friday
Here DAYOFWEEK is the MySQL function which yields Sunday=1,
Monday=2, and so on, up to Saturday=7. The inline NUMBERS table
has one column, N, and we apply the WHERE condition to
get a whole year's worth of Fridays.
|
