sql take all querys from last month

Question

I followed the first answer on this question but i have some problems with my sql query. I don´t get any result.
What error i made ?
How can i set CURDATE always on the first day of the month ?

SELECT DATE_FORMAT(`date`, '%Y-%m-%d') , `price` 
FROM `sales` 
WHERE `id` = :id 
AND (`date` BETWEEN CURDATE() - INTERVAL 30 DAY AND CURDATE())

Edit Whole code:

$verkaufmonat3 = 0;
$verkaufmonatanzahl2 = 0;
$verkaufmonat4 = array();
$sqluser5 = $X['dbh']->prepare("SELECT DATE_FORMAT(`date`, '%Y-%m-%d') , `preis` FROM `verkauf` WHERE `vertreterid` = :id AND (`date` BETWEEN CURDATE(), '%Y-%m-01' - INTERVAL 60 DAY AND CURDATE(), '%Y-%m-01')");
$sqluser5->execute(array(
':id'=>$_SESSION['id']
));
$verkaufmonatanzahl2 = $sqluser5->rowCount();
$verkaufmonat4 = $sqluser5->fetchAll();
for ($a = 0; $a <= $verkaufmonatanzahl2; $a++) {
        $verkaufmonat3 += $verkaufmonat2[$a]['preis'];
}

In my database i have a sale on the 28.10.2016 wich has for ex. a value of 50 eur. So my $verkaufmonat3 should be 50 but it isn´t, it´s 0.

http://stackoverflow.com/questions/11808232/how-do-i-get-the-first-day-of-the-current-month, http://blog.sqlauthority.com/2014/04/09/mysql-finding-first-day-and-last-day-of-a-month/, — ADyson, Nov 16 '16 at 15:01
I believe the problem is you specify id here while you need to have dates range only — Stan, Nov 16 '16 at 15:03

Juan Carlos Oropeza · Accepted Answer · 2016-11-16T15:28:48.493

5

Your question isnt clear but to get the first day of current month then you use

 SELECT DATE_FORMAT(CURDATE() , '%Y-%m-1')

So if you want the previous month data try

SELECT 
    DATE_FORMAT(CURDATE() , '%Y-%m-1') - INTERVAL 1 MONTH as first_day,
    DATE_FORMAT(CURDATE() , '%Y-%m-1') - INTERVAL 1 DAY as last_day

MEANING

 WHERE `date` BETWEEN  DATE_FORMAT(CURDATE() , '%Y-%m-1') - INTERVAL 1 MONTH
                  AND  DATE_FORMAT(CURDATE() , '%Y-%m-1') - INTERVAL 1 DAY

edited Nov 16 '16 at 15:28

answered Nov 16 '16 at 15:02

Juan Carlos Oropeza

47,252
12
78
118

Thanks a lot, that was the second problem i had. The main problem/question was wich error i made because it does´nt work. – delato468 Nov 16 '16 at 15:04
You have to explain a litle more what is the problem, show us some sample data and expected result. – Juan Carlos Oropeza Nov 16 '16 at 15:05
Like that you mean: SELECT DATE_FORMAT(CURDATE() , '%Y-%m-01') - INTERVAL 1 MONTH as first_day, DATE_FORMAT(CURDATE() , '%Y-%m-01') - INTERVAL 1 DAY as last_day, `preis` FROM `verkauf` WHERE `vertreterid` = :id // no result – delato468 Nov 16 '16 at 15:30
I just saw your edit. Try your query without date first. Im thinking the problem is with the id and not the dates. I wrote the `SELECT` so you can see the dates creates, you need to use the `WHERE` sintaxis, but again, I have a feeling your problem is the `id` – Juan Carlos Oropeza Nov 16 '16 at 15:32
Oh god im so sorry there was another error: $verkaufmonat3 += $verkaufmonat2[$a]['preis']; instead of $verkaufmonat3 += $verkaufmonat4[$a]['preis']; // now it is working thanks a lot, withouth your help i didn´t get it. – delato468 Nov 16 '16 at 15:39
Still you need to check your date range... using `INTERVAL 60 DAYS` can give you data for 3 month if FEB is on the range. – Juan Carlos Oropeza Nov 16 '16 at 15:41

sql take all querys from last month

1 Answers1