Creating SQL Reports Based on Date Criteria

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');