Please help with the following problem statement in MYSQL:
i have a start_date and end_date. I need to calculate number of days between start_date and end_date for the current month and till current date.
Example1:
start_date = "2020-06-27"
end_date = "2020-09-27"
Since today's date is 2020-08-11 (11th Aug 2020). I need 11 as the output.
Example2:
start_date = "2020-08-08"
end_date = "2020-08-09"
I need 2 as the output.
Example3:
start_date = "2020-08-08"
end_date = "2020-08-14"
I need 4 as the output because current date is 11th Aug.
I have tried, datediff, period_diff but haven't got any success yet. Please, seeking some help here.
Thanks for looking at the problem statement.
I think it is this:
SELECT DATEDIFF(
LEAST(DATE_FORMAT(NOW(),"%Y-%m-%d"),"2020-08-14"),
GREATEST("2020-08-08",DATE_FORMAT(NOW(),"%Y-%m-01"))
)+1;
The answer to your second question needs to be 2, I guess
You can use the date functions of MySql with least() and greatest() to adjust the boundaries:
select datediff(
least(end_date, current_date),
greatest(start_date, last_day(current_date - interval 1 month) + interval 1 day)
) + 1 difference
from tablename
See the demo.
Results:
| difference |
| ---------- |
| 11 |
| 2 |
| 4 |
You can try MONTH function of MySQL with a combination of DATEDIFF, something like
select
case
WHEN (MONTH(startDate)!==MONTH(CURDATE()) && MONTH(endDate)!==MONTH(CURDATE()))
THEN DATEDIFF(STR_TO_DATE(ADD_START_DATE_OF_MONTH,'%d,%m,%Y'),CURDATE()) AS days
WHEN MONTH(startDate)!==MONTH(CURDATE())
THEN DATEDIFF(CURDATE(),STR_TO_DATE(endDate, '%m/%d/%Y')) AS days
WHEN MONTH(endDate)!==MONTH(CURDATE())
THEN DATEDIFF(STR_TO_DATE(startDate, '%m/%d/%Y'), CURDATE()) AS days
ELSE
DATEDIFF(STR_TO_DATE(startDate, '%m/%d/%Y'), STR_TO_DATE(endDate, '%m/%d/%Y')) AS days