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