Home arrow SQL arrow Page 3 - Generating Reports with SQL Date Handling
SQL

Generating Reports with SQL Date Handling


In this conclusion to a four-part series on SQL date handling, you will learn how to generate quarterly reports -- and how to hack the technique to generate reports at other times covering other periods of time. This article is excerpted from chapter four of the book SQL Hacks, written by Andrew Cumming and Gordon Russell (O'Reilly, 2006; ISBN: 0596527993). Copyright © 2006 O'Reilly Media, Inc. All rights reserved. Used with permission from the publisher. Available from booksellers or direct from O'Reilly Media.

Author Info:
By: O'Reilly Media
Rating: 4 stars4 stars4 stars4 stars4 stars / 15
December 27, 2007
TABLE OF CONTENTS:
  1. · Generating Reports with SQL Date Handling
  2. · Hacking the Hack
  3. · HACK 23 Second Tuesday of the Month
  4. · The Formula
  5. · Hacking the Hack: The Last Thursday of the Month

print this article
SEARCH DEVARTICLES

Generating Reports with SQL Date Handling - HACK 23 Second Tuesday of the Month
(Page 3 of 5 )

You can find “floating” calendar dates, such as the second Tuesday of the month, with modular arithmetic and brute force reasoning.

The formula to calculate the second Tuesday of the month depends on the day of the week of the first day of the month. But which month? Obviously, all you need to know are the year and month, but it’s easier if you start with a date: the date of the first day of that month. For testing purposes, use the following table:

  CREATE TABLE monthdates(monthdate DATE NOT NULL PRIMARY KEY);
  INSERT INTO monthdates(monthdate) VALUES (DATE '2007-04-01');
  INSERT INTO monthdates(monthdate) VALUES (DATE '2007-05-01');
  INSERT INTO monthdates(monthdate) VALUES (DATE '2007-06-01');

So, given a date, the first day of some month, what date is the second Tuesday of that month? The process for obtaining the solution begins by calculating the day of the week for the first day of the month.

Day-of-Week Function

Although standard SQL does not provide a function to give the day of the week for any date, most database systems do. Table 4-5 shows some of the functions that can accomplish this.

Table 4-5. Day-of-the-week functions 

Database

Function

MySQL

DAYOFWEEK(date)

Oracle

TO_CHAR(date,'D')

SQL Server

DATEPART(DW,date)

PostgreSQL

EXTRACT(dow FROM date)

Access

DatePart("w", date)

The essence of these functions is that they will return a number between 0 and 6, or between 1 and 7. Sometimes 0 (or 1) is Sunday and 6 (or 7) is Saturday, and sometimes 0 (or 1) is Monday and 6 (or 7) is Sunday.

Make sure you know how your database system is set up, because some database systems have local settings that affect the weekday number returned, such asNLS_TERRITORYin Oracle andDATEFIRSTin SQL Server. MySQL offersWEEKDAY(date), which returns 1 (Monday) through 7 (Sunday), as well asDAYOFWEEK(date), which returns 1 (Sunday) through 7 (Saturday).

The following formula uses the range 1 (Sunday) through 7 (Saturday). If your database system has no easy way to produce this range, but you can produce some other similar range, then you can alter the formula easily once you understand how it works.


blog comments powered by Disqus
SQL ARTICLES

- Focusing SQL Queries
- Complex SQL Queries
- A Close Look at the SQL Query
- Generating Reports with SQL Date Handling
- Creating SQL Reports Based on Date Criteria
- SQL Date Handling and Data Trends
- Date Handling
- Introduction to SQL
- Lies, Damn Lies, Statistics, and SQL

Watch our Tech Videos 
Dev Articles Forums 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us 
Weekly Newsletter
 
Developer Updates  
Free Website Content 
Contact Us 
Site Map 
Privacy Policy 
Support 

Developer Shed Affiliates

 




© 2003-2017 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap
Popular Web Development Topics
All Web Development Tutorials