Getting the difference of the two same dates MYSQL

Question

Hi guys here is my coode:

SELECT SUM(DATEDIFF(L_End, L_Start)), L_Start,L_End
        FROM leave_table 
        where ID_No = '9283158370' and L_Status = 'Approve' and L_Type ='Sick'

That is working but my dilemma is what if the start and end date are the same, like for example start date is 2015-03-10 and end date is also 2015-03-10. If I query that in the DATEDIFF function the difference is 0. What I would like to is date diff is equal to 1. Since It the start and the end date is 1 day.

have a look here, I guess. Construct a conditional in your SUM(); http://stackoverflow.com/questions/16528906/t-sql-if-statement-embedded-in-a-sum-function or here: http://stackoverflow.com/questions/8732036/mysql-sum-query-with-if-condition — Cornel Raiu, Mar 11 '15 at 14:20

score 1 · Accepted Answer · answered Mar 11 '15 at 14:25

1

you will need something like:

SELECT SUM(CASE WHEN DATEDIFF(L_End, L_Start) = '0' THEN 1 ELSE DATEDIFF(L_End, L_Start) END) as summed_column, L_Start,L_End
    FROM leave_table 
    where ID_No = '9283158370' and L_Status = 'Approve' and L_Type ='Sick'

answered Mar 11 '15 at 14:25

Cornel Raiu

2,758
3
22
31

1

Thank you! I didn't know I could do that in mysql. Again thank you soo much! – user3797088 Mar 11 '15 at 14:32

Getting the difference of the two same dates MYSQL

1 Answers1