Calculate leave days based on leaveStart and leaveEnd

[Roy Harper]

I am trying to calculate the total leave days based on two dates. I tried using datediff and timestampdiff but it gives me the wrong result.
For example I input:
leaveStart = "2022-04-26"
leaveEnd = "2022-04-27"

So the total leave days should be 2 days because the leave is from 26 to 27.

datediff and timestampdiff basically gives me the difference between two dates.
Any ideas how to compute it properly? I may try adding 1 day to the result but i just want to know if there's a better way to do it.

 

[JosiahMaybe]

I think use text format to break it into year, month, and day, then difference for each, then how long a month or year is in days each, then have result. You may break using 0 to first index of - and then firat index of - +1 to last index of - then last index of - +1 to end and be sure to test for valid numbers. Sorry if you wanted code written for you. You could use an array or rule set for days in a month and every 4 years is a leap year. X E.

 

[venkatesh]

To calculate the total leave days correctly based on two dates in MariaDB, you can use the DATEDIFF function and adjust the result to include both start and end dates. The DATEDIFF function returns the difference in days between two dates, but it does not include the end date in its count. To account for this, you can simply add 1 to the result.

SELECT DATEDIFF(leaveEnd, leaveStart) + 1 AS totalLeaveDaysFROM your_tableWHERE leaveStart = '2022-04-26' AND leaveEnd = '2022-04-27';

In this example:

• DATEDIFF(leaveEnd, leaveStart) calculates the difference between the two dates.
• Adding 1 includes both the start and end dates in the total count.

If you want a more general solution that allows you to input any two dates, you could use:

SET @leaveStart = '2022-04-26';SET @leaveEnd = '2022-04-27';SELECT DATEDIFF(@leaveEnd, @leaveStart) + 1 AS totalLeaveDays;