MYSQL - Calculate days in the current month between start date and end date

user_405818 0
Viewed 146

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.

mysql
3 Answers

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

user_35420970

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          |
user_104988280

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

All date-time functions of mysql

user_24272560
Related