Learn how to build date generators without loops using SQL, and some useful techniques to help you manipulate date and time data.

by Alex Kozak

Feb 20, 2007

Page 3 of 3

Create Your Own Calendars
Almost every programmer has tried to create a calendar. In addition to the homemade variety, you'll find a lot of third-party calendars and many software products offer built-in calendars. However, those calendars have one significant drawbackyou can't change their layout or traditional designation on the fly. Usually you can make changes only in their presentation layers.

This section discusses SQL Server 2005's new PIVOT operator and how you can use it to build traditional or unusual calendars. To start the example, create the following simple query for number of days in the months of 2007:

Month January February March April May June July August September October November December
--------- ------- -------- ----- ----- --- ---- ---- ------ --------- ------- -------- --------
#weekends 8 8 9 9 8 9 9 8 10 8 8 10

If you're a contract worker who is paid hourly and has an eight-hour workday, then you may want to know the number of working hours in each month. In that case, you will find the following script very helpful:

Month January February March April May June July August September October November December
---------- ------- -------- ----- ----- --- ---- ---- ------ --------- ------- -------- --------
#WorkHours 184 160 176 168 184 168 176 184 160 184 176 168

In real life, you may have fewer working hours in a month because of the holidays. In that case, you can place the holiday dates in a separate table and modify the script from the above listing (try this as an exercise).

Finally, the following example shows you how to build a traditional calendar using the PIVOT operator:

Time and Date Manipulations
The flexible techniques shown in this article can be useful in projects when you need to generate and manipulate date/time data. Although the examples are implemented in SQL Server, you can easily adjust them for other RDBMS, because many RDBMS have similar date/time functions.