select all records from previous month

kwillems

I want so select all records from the previous month, using a Mysql-query.
For the moment i've made something like this:

$query = "SELECT * FROM tabel WHERE MONTH(datum) = MONTH(DATE_SUB(NOW(), INTERVAL 1 MONTH))";

But as you can see this won't work perfect. For example: in januari it won't select the records from december, and so on.

Can anybody help me out? The date are stored in a datetime-field called 'datum'.

devinemke

SELECT * FROM tabel WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= datum

kwillems

Thanx, but that doesn't do the trick.
Your query selects all the records younger than 30 days ago. For example: it also selects records which have a date in the future.

I want to select the records from the previous mont. For example: on the 5 of july i want to select all the records which have a date starting on 1 june and ending on 30 june (of the same year).And in januari i want to select the records from december of the previous year of course.

devinemke

just add that clause:

SELECT * FROM table WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= datum AND datum <= NOW()

kwillems

Finally i did it like this:

<?php
$query = "SELECT * FROM tabel
WHERE
CASE WHEN MONTH(NOW()) > 1
THEN YEAR(datum) = YEAR(NOW()) AND MONTH(datum) = MONTH(DATE_SUB(NOW(), INTERVAL 1 MONTH))
ELSE YEAR(datum) = YEAR(NOW())-1 AND MONTH(datum) = MONTH(DATE_SUB(NOW(), INTERVAL 1 MONTH))
END";
?>