SQL for last month

[Daryl]

I am using the following query to find records for last month

SELECT * FROM application JOIN company JOIN users WHERE YEAR(application.application_date) = YEAR(CURDATE()) AND MONTH(application.application_date) = MONTH(CURDATE())-1 AND application.consultant_company = company.co_id AND application.consultant= users.id ORDER BY application.application_date DESC

However this does not work this month for last month. Any idea why and what I should change the sql too to get it to work?

[Ray Borduin]

That is because you are using:

MONTH(CURDATE())-1

and this being month 1, then that would look for a return value of month 0, when you actually want month 12.

Instead try:

MONTH(CURDATE() - INTERVAL 1 MONTH)

[Daryl]

Thanks Ray

But this doesn't show results from December 2010 either.

[Ray Borduin]

That is because you would also have to use:

YEAR(application.application_date) = YEAR(CURDATE() - INTERVAL 1 MONTH)

[Daryl]

Thanks Ray!!

Would

YEAR(CURDATE() - INTERVAL 2 MONTH)

show data from 2 months ago?

[Ray Borduin]

yes it would.

[Daryl]

Excellent!

Thanks again!

[Daryl]

What is the best code for PHP to do the same?

I am using;

For this month
<?php $date = date('F'); echo "$date";?>

For 1 month ago
<?php
$mnth = date('F');
$prev_mnth = date('F',strtotime("-1 months")); // "previous month" works also
echo $prev_mnth;
?>

But they are both showing March today

For 2 months ago
<?php
$mnth = date('F');
$prev_mnth = date('F',strtotime("-2 months")); // "previous month" works also
echo $prev_mnth;
?>

But this shows January as expected...

[Daryl]

This solution shows March instead of February too...

<?php
$last_month_time = mktime(0, 0, 0, date("m")-1, date("d"), date("Y"));
$last_month = date('F', $last_month_time);
echo "$last_month";
?>

[Daryl]

This one works!

<?php echo date('F', mktime(0, 0, 0, date('m')-1, 1, date('Y')));
?>