Home arrow SQL arrow Page 5 - 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 - Hacking the Hack: The Last Thursday of the Month
(Page 5 of 5 )

You can use a similar technique to calculate the last Thursday of the month. Just find the first Thursday of next month and subtract seven days.

The formula for the offset for the first Thursday of the month beginning withmonthdateis:

  (12-DAYOFWEEK(monthdate) ) % 7

You should subtract from 12 because Thursday is represented by 5 and 5 + 7 = 12.

The query to get the first day of next month is:

  mysql> SELECT monthdate AS first_day_of_month
      ->       ,DATE_ADD(monthdate,INTERVAL 1 MONTH)
    
->             AS first_day_of_next_month
    
->   FROM monthdates;  

 

 

 

first_day_of_month first_day_of_next_month

 

 2007-04-01

2007-05-01

 

2007-05-01

 2007-06-01

 

 2007-06-01

 2007-07-01 

 

 

You can use this result to find the first Thursday of next month and subtract 7 to get the last Thursday of this month:

  mysql> SELECT first_day_of_month
      ->        ,DATE_ADD(first_day_of_next_month
    
->                 ,INTERVAL
    
->                   ((12 - DAYOFWEEK(first_day_of_next_month)) % 7)
    
->                         - 7 DAY) AS last_thursday_of_month
    
->   FROM
    
->        (SELECT monthdate AS first_day_of_month
    
->                ,DATE_ADD(monthdate,INTERVAL 1 MONTH)
    
->                            AS first_day_of_next_month
    
->             FROM monthdates) t;
   

 

 

 

 first_day_of_month  last_thursday_of_month 

 

 2007-04-01

 2007-04-26

 

 2007-05-01

 2007-05-31

 

 2007-06-01

 2007-06-28

 

 

See Also

  •  “Generate a Calendar” [Hack #87]

    —Rudy Limeback 


DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware.

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