Q

Populate a calendar table

I want to create a table with five columns (weekdayNumber, weekdayName, date, weekdayBeg, weekdayEnd). How can I create and generate data automatically?

I want to create a table with five columns (weekdayNumber, weekdayName, date, weekdayBeg, weekdayEnd). How can I create and generate data automatically?

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?

This was first published in October 2005

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close