Team LiB   Previous Section   Next Section

5.6 Printing Calendars

5.6.1 Problem

You want to print a calendar, possibly for the entire year, in a nice-looking format.

5.6.2 Solution

The following query, which uses the Pivot table first introduced in Chapter 1, generates a set of sequential numbers, which are then transformed into dates and printed as a calendar: 

SELECT  
   STR(YEAR(CAST('2001-1-1' AS DATETIME)+i-6))+ SPACE(1)+
      SUBSTRING('JANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC',
      MONTH(CAST('2001-1-1' AS DATETIME)+i)*3-2,3) Month,
   DAY(CAST('2001-1-1' AS DATETIME)+i-6) AS S,
   DAY(CAST('2001-1-1' AS DATETIME)+i-5) AS M,
   DAY(CAST('2001-1-1' AS DATETIME)+i-4) AS T,
   DAY(CAST('2001-1-1' AS DATETIME)+i-3) AS W,
   DAY(CAST('2001-1-1' AS DATETIME)+i-2) AS T,
   DAY(CAST('2001-1-1' AS DATETIME)+i-1) AS F,
   DAY(CAST('2001-1-1' AS DATETIME)+i) AS S 
FROM Pivot
WHERE  DATEPART(dw,CAST('2001-1-1' AS DATETIME)+i)%7=0
ORDER BY i

The output from this query is a calendar, starting with the week of the initial date, which in this case is 2001-1-1: 

Month          S    M    T    W    T    F    S           
-------------- ---- ---- ---- ---- ---- ---- ---- 
      2000 DEC 30   31   1    2    3    4    5
      2001 JAN 6    7    8    9    10   11   12
      2001 JAN 13   14   15   16   17   18   19
      2001 JAN 20   21   22   23   24   25   26
      2001 JAN 27   28   29   30   31   1    2
      2001 FEB 3    4    5    6    7    8    9
      2001 FEB 10   11   12   13   14   15   16
      2001 FEB 17   18   19   20   21   22   23
      2001 FEB 24   25   26   27   28   1    2
      2001 MAR 3    4    5    6    7    8    9
...

5.6.3 Discussion

This solution demonstrates the use of a Pivot table (named Pivot in our example) to generate a sequence of dates. Look carefully at this query, and you'll see where we added a constant number to a set of DATETIME values rather than using the DATEADD function. We did this for readability. For example, rather than use DAY(CAST('2001-1-1' AS DATETIME)+i-6), we could have used DATEADD(day,i+6, CAST('2001-1-1' AS DATETIME)).

The first line of the SELECT statement extracts year information from the input date. Similarly, it extracts the month and then takes the appropriate month abbreviation from the string containing all possible abbreviations. The year and month information are then combined to form a label for each row of output.

In each row of output, you see the dates for the seven days of the week in question. We directly print only every seventh date (the Saturday), while all other days are calculated by subtracting the appropriate number of days from it.

Your initial reaction to this recipe might be to wonder why we didn't use addition instead of subtraction when calculating the dates for the different days of the week. Here, subtraction is more appropriate than addition. The WHERE clause results in a set of values from the Pivot table that, when added to the input date, yield a list of Saturdays. The first date returned will be that of the Saturday immediately following the input date. Subtraction is used to work backwards through the other days in that week. If addition were used, the days between the first Saturday and the initial date would be skipped. By using subtraction, we get dates for all days in the first week.

Rather than extracting the month abbreviation from a string constant, we could also use the DATENAME function. However, there are some problems with its use. The most important problem is that DATENAME returns names of months according to your current operating system language settings. Language settings can sometimes be wrong, so we prefer to use a string constant containing the month abbreviations. Nevertheless, in case you prefer to use DATENAME, the following is a DATENAME version of this recipe's solution: 

SELECT  
   STR(YEAR(CAST('2001-1-1' AS DATETIME)+i-6))+ SPACE(1)+
   DATENAME(month, CAST('2001-1-1' AS DATETIME)+i) Month,
   DAY(CAST('2001-1-1' AS DATETIME)+i-6) AS S,
   DAY(CAST('2001-1-1' AS DATETIME)+i-5) AS M,
   DAY(CAST('2001-1-1' AS DATETIME)+i-4) AS T,
   DAY(CAST('2001-1-1' AS DATETIME)+i-3) AS W,
   DAY(CAST('2001-1-1' AS DATETIME)+i-2) AS T,
   DAY(CAST('2001-1-1' AS DATETIME)+i-1) AS F,
   DAY(CAST('2001-1-1' AS DATETIME)+i) AS S 
FROM Pivot
WHERE  DATEPART(dw,CAST('2001-1-1' AS DATETIME)+i)%7=0
ORDER BY i

Following is the result if the system has a different language set (in this case Slovenian) and runs the above query: 

Month               S    M    T    W    T    F    S           
------------------- ---- ---- ---- ---- ---- ---- ---- 
      2000 December 30   31   1    2    3    4    5
      2001 Januar   6    7    8    9    10   11   12
      2001 Januar   13   14   15   16   17   18   19
      2001 Januar   20   21   22   23   24   25   26
      2001 Januar   27   28   29   30   31   1    2
      2001 Februar  3    4    5    6    7    8    9
      2001 Februar  10   11   12   13   14   15   16
      2001 Februar  17   18   19   20   21   22   23
      2001 Februar  24   25   26   27   28   1    2
      2001 Marec    3    4    5    6    7    8    9
...

Check the same code on your system, and you might get a different result if your system is set to a different language. This code clearly demonstrates how operating-system settings can affect results from date functions. We recommend that you use code dependent on operating-system settings only when you control those settings or when you need the settings to get the desired results.

    Team LiB   Previous Section   Next Section