How to make it to select from 1st day every month

Hello there I have a question so I have a goal mysqli query and it sum the price and select by interval 1 month I try few things to made it to select only from 1st day of the month but I have no idea anymore how to do it..

So here is my code

$monthquery=mysqli_query($link,"SELECT SUM(price) FROM `transactions` WHERE `date` BETWEEN date_sub(CAST(CURRENT_TIMESTAMP AS DATE),INTERVAL 1 MONTH) AND CAST(CURRENT_TIMESTAMP AS DATE);")->fetch_assoc();
$month = $monthquery["SUM(price)"];

$percent = 0;
$percent = ($month*100)/$donation_goal;
$goal = round($percent, 2);

​x
 
$monthquery=mysqli_query($link,"SELECT SUM(price) FROM `transactions` WHERE `date` BETWEEN date_sub(CAST(CURRENT_TIMESTAMP AS DATE),INTERVAL 1 MONTH) AND CAST(CURRENT_TIMESTAMP AS DATE);")->fetch_assoc();$month = $monthquery["SUM(price)"];​$percent = 0;$percent = ($month*100)/$donation_goal;$goal = round($percent, 2);​

I want it to select let say If the customer start from 20/12/2019 and everything have to be reset on 01/01/2020, not like now from 20th to 20th.. I saw few things here on stackoverflow I try them and it does not always it give me same result.

Thanks anyone.

Answer

select only from 1st day of the month

You can do:

 `date` >= DATE_FORMAT(NOW() ,'%Y-%m-01')

 
 `date` >= DATE_FORMAT(NOW() ,'%Y-%m-01')​

Advertisement

Answer