Build Date Generators and Manipulate Date and Time Data in SQL : Page 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:

SELECT '#days' AS Month, * 
  FROM 
   (SELECT DATEADD(dd, num, 'Dec 31, 2006') days, 
	       DATENAME(mm, DATEADD(dd, num, 'Dec 31, 2006')) months
	  FROM sequence) AS SourceTable
PIVOT
(COUNT(days)
FOR months IN([January],[February],[March],[April],[May],[June],
[July],[August], [September],[October],[November],[December])
) AS PivotTable

Results:

Month January February March April May June July August September October November December
----- ------- -------- ----- ----- --- ---- ---- ------ --------- ------- -------- --------
#days 31       28        31   30   31   30   31    31      30        31       30        31

Next, you may want to know how many weekends are in each month. Instead of going to the calendar and counting the weekends, you can write the following simple query:

SELECT *
  FROM
   (SELECT '#weekends' Month,
       DateName(mm, DATEADD(dd, num, 'Dec 31, 2006')) MonthName,
       DATEPART(dd, DATEADD(dd, num, 'Dec 31, 2006')) YearDay 
     FROM sequence
     WHERE DateName(dw, DATEADD(dd, num, 'Dec 31, 2006')) 
       IN ('Sunday','Saturday')) sourceTable
PIVOT 
(COUNT(YearDay)
FOR MonthName IN ([January],[February],[March],[April],[May],[June],[July],
[August], [September],[October],[November],[December])) AS pivotTable;

Results:

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:

SELECT *
FROM 
  (SELECT '#WorkHours' AS [Month],
		DateName(mm, DATEADD(dd, num, 'Dec 31, 2006')) MonthName,
		COUNT(DATEADD(dd, num, 'Dec 31, 2006'))*8 AS  WorkingHours
     FROM sequence
     WHERE DateName(dw, DATEADD(dd, num, 'Dec 31, 2006')) 
           IN ('Monday','Tuesday','Wednesday','Thursday','Friday')
     GROUP BY DatePart(yy, DATEADD(dd, num, 'Dec 31, 2006')),
			DateName(mm, DATEADD(dd, num, 'Dec 31, 2006'))
   ) sourceTable
PIVOT 
(SUM(WorkingHours)
FOR MonthName 
IN([January],[February],[March],[April],[May],[June],[July],
[August], [September],[October],[November],[December])) AS pivotTable;

Results:

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:

SELECT *
  FROM
  (SELECT DatePart(mm, DATEADD(dd, num, 'Dec 31, 2006')) MonthNum,
          DateName(mm, DATEADD(dd, num, 'Dec 31, 2006')) MonthName,
          DatePart(wk, DATEADD(dd, num, 'Dec 31, 2006')) WeekOfYear,
          DATEPART(dd, DATEADD(dd, num, 'Dec 31, 2006')) YearDay,
          DateName(dw, DATEADD(dd, num, 'Dec 31, 2006')) weekDayName	 
    FROM sequence) sourceTable
PIVOT 
(MIN(YearDay) 
FOR weekDayName 
IN
(Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday)) AS pivotTable
ORDER BY MonthNum, WeekOfYear;

Results:

#Month MonthName  WeekOfYear  Sunday Monday Tuesday Wednesday Thursday Friday Saturday
------ --------- ----------- ------- ------ ------- --------- -------- ------ --------
1      January    1           NULL   1      2       3         4        5      6
1      January    2           7      8      9       10        11       12     13
1      January    3           14     15     16      17        18       19     20
1      January    4           21     22     23      24        25       26     27
1      January    5           28     29     30      31        NULL     NULL   NULL
2      February   5           NULL   NULL   NULL    NULL      1        2      3
2      February   6           4      5      6       7         8        9      10
2      February   7           11     12     13      14        15       16     17
2      February   8           18     19     20      21        22       23     24
2      February   9           25     26     27      28        NULL     NULL   NULL
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 
11     November   46          11     12     13      14        15       16     17
11     November   47          18     19     20      21        22       23     24
11     November   48          25     26     27      28        29       30     NULL
12     December   48          NULL   NULL   NULL    NULL      NULL     NULL   1
12     December   49          2      3      4       5         6        7      8
12     December   50          9      10     11      12        13       14     15
12     December   51          16     17     18      19        20       21     22
12     December   52          23     24     25      26        27       28     29
12     December   53          30     31     NULL    NULL      NULL     NULL   NULL

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.

Previous Page