Populate a calendar table

Despite the fact that SQL is a standard language, no two database systems have the same date functions. Therefore...

to illustrate how to create such a table, which is usually called a calendar table, we must pick one SQL variant for illustrative purposes, and this time it will be Microsoft SQL Server.

The main "trick" we need here is to use an integers table. I've written about the integers table before, because it's useful in so many ways, and this is one of them. So let's just recap how to create it:

create table integers (i integer)
insert into integers (i) values (0) 
insert into integers (i) values (1) 
insert into integers (i) values (2) 
insert into integers (i) values (3) 
insert into integers (i) values (4) 
insert into integers (i) values (5) 
insert into integers (i) values (6) 
insert into integers (i) values (7) 
insert into integers (i) values (8) 
insert into integers (i) values (9)   

Create the calendar table:

create table calendar
( date          datetime   not null primary key
, weekdayNumber tinyint    not null
, weekdayName   varchar(9) not null
, weekdayBeg    datetime   not null
, weekdayEnd    datetime   not null
)

It wasn't clear what you had in mind with the columns weekdayBeg and weekdayEnd, so I took a guess and decided you wanted the actual dates of the Sunday (first day) and Saturday (last day) of the week containing the date.

Now let's populate the calendar table:

declare @startdate datetime
set @startdate = '2005-01-01'
insert 
  into calendar
select dateadd(day,n,@startdate)
     , datepart(dw,dateadd(day,n,@startdate))
     , datename(dw,dateadd(day,n,@startdate))
     , dateadd(day
            ,1-datepart(dw,dateadd(day,n,@startdate))
            ,dateadd(day,n,@startdate))
     , dateadd(day
            ,7-datepart(dw,dateadd(day,n,@startdate))
            ,dateadd(day,n,@startdate))
  from (
       select h.i*100+t.i*10+u.i  as n
         from integers u
            , integers t
            , integers h
       ) as numbers
 where n between 0 and 365
order
    by n

The "numbers" subquery is a simple cross join, in which the integers 0 through 9 are combined three times as units, tens and hundreds. This creates 1,000 numbers (from 0 through 999) but the outer query selects only those between 0 and 365, thus generating 366 dates. To generate more than one year's worth of dates, simply adjust the range of numbers selected, or run the query multiple times with different starting dates.

There isn't really too much to say about the date functions. These are very specific to Microsoft SQL Server, and other database systems will have similar functions.

dateadd(day,n,@startdate) is the date. datepart(dw,...) is the day of week, 1 through 7, where 1=Sunday and 7=Saturday. datename(dw,...) is the name of the day of week, i.e. Sunday, Monday, etc. Finally, the last two expressions use the day of week number (which is between 1 and 7, remember) to calculate the dates of the Sunday and Saturday at the beginning and end of the week containing the date.

Neat, eh?