Login | Register   
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


advertisement
 

Build Date Generators and Manipulate Date and Time Data in SQL : Page 3

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


advertisement
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.



Alex Kozak is a senior DBA/analyst working for SAP Canada. He has more than 15 years of database and programming experience. Microsoft has included some of his articles in the MSDN Library.
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap