Returning every Saturday between two dates?

Databases
1

Having a major mental block with this, and was wondering if anyone can help me?

Basically I want to create query which will return every occurence of a Saturday between two dates.

Looking at the the DAYOFWEEK function, it doesn’t appear that you can only provide it with a specific date. The only other way round that I could think of is by having a prepopulated table with all dates within a year and the actual date occurence.

I was hoping to do this on the fly, as the query would be used as part of trigger.

2

you can do it with an integers table

how large a range of dates? i mean, how far apart are the startdate and enddate that contain the saturdays that you want to generate?

3

I was thinking about 1 year, however in some instances it may be a month.

4

okay, first create the integers table –

create table integers (i integer not null primary key);
insert into integers (i) values
(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

next, run this query to generate a series of integers 0 through 999 –

select H.i*100 + T.i*10 + U.i + 1 as N
  from integers as H
cross
  join integers as T
cross
  join integers as U

make sure you understand how this query works

finally, plug in your start and end dates into the following query –

select date_add('[i]startdate[/i]'
         , interval H.i*100 + T.i*10 + U.i day ) as Sunday
  from integers as H
cross
  join integers as T
cross
  join integers as U
 where dayofweek(
       date_add('[i]startdate[/i]'
         , interval H.i*100 + T.i*10 + U.i day )
                ) = 1
   and date_add('[i]startdate[/i]'
         , interval H.i*100 + T.i*10 + U.i day )
           <= '[i]enddate[/i]'
order
    by Sunday

example: startdate ‘2006-12-18’, enddate ‘2007-03-18’

select date_add('2006-12-18'
         , interval H.i*100 + T.i*10 + U.i day ) as Sunday
  from integers as H
cross
  join integers as T
cross
  join integers as U
 where dayofweek(
       date_add('2006-12-18'
         , interval H.i*100 + T.i*10 + U.i day )
                ) = 1
   and date_add('2006-12-18'
         , interval H.i*100 + T.i*10 + U.i day )
           <= '2007-03-18'
order
    by Sunday

Sunday
2006-12-24
2006-12-31
2007-01-07
2007-01-14
2007-01-21
2007-01-28
2007-02-04
2007-02-11
2007-02-18
2007-02-25
2007-03-04
2007-03-11
2007-03-18

sweet, eh? :slight_smile: :slight_smile:

5

uh oh, did i generate the Sundays? you wanted Saturdays!!

okay, change the DAYOFWEEK value from 1 to 7

:slight_smile:

6

Wow very impressed!

Thanks for taking the time to explain that to me, can see this coming in very useful and certainly way more extendable that having to prepopulate with dates!

7

A great explanation and answer as always!

8