Creating SQL Reports Based on Date Criteria - Current Month

(Page 3 of 4 )

If you want to see the data for the current month you can test both month and year in theWHEREclause:

  mysql> SELECT * FROM t
      ->  WHERE MONTH(whn)=MONTH(CURRENT_DATE)
      ->    AND  YEAR(whn)=YEAR(CURRENT_DATE)
      ->  ORDER BY whn;
  +------------+------+
  | whn        | v    |
  +------------+------+
  | 2006-06-07 |  96  |
  | 2006-06-11 |   4  |
  | 2006-06-12 |  78  |
  | 2006-06-12 |  36  |
  | 2006-06-17 |  57  |
  | 2006-06-29 |  74  |
  | 2006-06-29 |  94  |
  | 2006-06-30 |  26  |
  +------------+------+

If you want to see the data for the preceding month do not changeMONTH(whn)=MONTH(CURRENT_DATE)toMONTH(whn)=MONTH(CURRENT_DATE)-1. If you do that you will get data from the wrong year when you run this query in January. Instead, you need to subtract one month fromCURRENT_DATE. It is a little neater if you do the date calculation in a nestedSELECT:

  mysql> SELECT * FROM t,
     ->   (SELECT CURRENT_DATE - INTERVAL 1 MONTH lastMnth) p
     ->  WHERE MONTH(whn)=MONTH(lastMnth)
     ->    AND YEAR(whn)=YEAR(lastMnth);
  +------------+------+------------+
  | whn        | v    | lastMnth   |
  +------------+------+------------+
  | 2006-05-04 |  43  | 2006-05-23 |
  | 2006-05-06 |  55  | 2006-05-23 |
  | 2006-05-08 |  89  | 2006-05-23 |
  | 2006-05-15 |  87  | 2006-05-23 |
  | 2006-05-22 |  90  | 2006-05-23 |
  | 2006-05-29 |  22  | 2006-05-23 |
  | 2006-05-30 |  27  | 2006-05-23 |
  +------------+------+------------+  

SQL Server.In SQL Server, you should use theDATEADDfunction. You can specify the interval asmfor month and the number of months as-1:

  SELECT * FROM t,
    (SELECT DATEADD(m,-1,GETDATE()) lastMnth) p
    WHERE MONTH(whn)=MONTH(lastMnth)
      AND YEAR(whn)=YEAR(lastMnth);

Oracle.You need to use thedualtable in a subselect in Oracle. Also, you can use theTO_CHARfunction to match the year and month in a single function:

  SELECT * FROM t,
    (SELECT CURRENT_DATE - INTERVAL '1' MONTH lastMnth FROM dual)
    WHERE TO_CHAR(whn,'yyyymm')=TO_CHAR(lastMnth,'yyyymm');

Next: Year-to-Date Totals >>
 

More SQL Articles
More By O'Reilly Media

Comments On: Creating SQL Reports Based on Date Criteria

· This article is an excerpt from the book "SQL Hacks," published by O'Reilly. We hope...   >>> Post your comment now!

Buy this book...

This article is excerpted from chapter four of the book SQL Hacks, written by Andrew Cumming and Gordon Russell (O'Reilly, 2006; ISBN: 0596527993). Check it out today at your favorite bookstore. Buy this book now.