2 Replies - 570 Views - Last Post: 19 April 2019 - 01:26 PM

#1 Prochotap   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 04-April 19

How to select range between Begin_date and End_date

Posted 18 April 2019 - 10:28 PM

In this case i have "leave" table:
+--------+---------+---------+-------------+---------+-----------+
|ID_LEAVE|ID_WORKER| BEGIN_DATE          | END_DATE              |
+--------+---------+---------+---------+------------+------------+
| 4      |   26    |2019-03-19 07:00:00  |2019-03-22 15:00:00    |  
+--------+---------+---------+----------------------+------------+


I wanna write mysql query which selects all in range between Begin Date and End date and then sum up. When i write like that:

SELECT *, 
time_format(SUM((datediff(END_DATE, BEGIN_DATE) + 1) * (time(END_DATE) - time(BEGIN_DATE))), '%H:%i:%s') AS 'LEAVE TIME' 
FROM leave 
WHERE (DATE(BEGIN_DATE) >= '2019-03-19' AND DATE(END_DATE) <='2019-03-22') 
GROUP BY ID_LEAVE;


Then shows like this:
    +--------+---------+---------+-------------+---------+------------------------+
    |ID_LEAVE|ID_WORKER| BEGIN_DATE          | END_DATE              | LEAVE_TIME |
    +--------+---------+---------+---------+------------+--------------------+-----
    | 4      |   26    |2019-03-19 07:00:00  |2019-03-22 15:00:00    |  32:00:00  |
    +--------+---------+---------+----------------------+------------+------------- 


But when i do like that:

SELECT *, 
time_format(SUM((datediff(END_DATE, BEGIN_DATE) + 1) * (time(END_DATE) - time(BEGIN_DATE))), '%H:%i:%s') AS 'LEAVE TIME' 
FROM leave 
WHERE (DATE(BEGIN_DATE) >= '2019-03-19' AND DATE(END_DATE) <='2019-03-21') 
GROUP BY ID_LEAVE;


It shows nothing.

I have a question: Is it possible to show all in (Begin_date and End_date) range? If yes to can someone please explain how to write it? Thanks for any answer.

Is This A Good Question/Topic? 0
  • +

Replies To: How to select range between Begin_date and End_date

#2 euro-space   User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 6
  • Joined: 21-January 19

Re: How to select range between Begin_date and End_date

Posted 19 April 2019 - 04:57 AM

Try this:

WHERE (DATE(BEGIN_DATE) >= '2019-03-19' AND DATE(END_DATE) <='2019-03-21')
OR (DATE(BEGIN_DATE) >= '2019-03-19' OR DATE(END_DATE) <='2019-03-21')
Was This Post Helpful? 0
  • +
  • -

#3 astonecipher   User is offline

  • Senior Systems Engineer
  • member icon

Reputation: 2997
  • View blog
  • Posts: 11,542
  • Joined: 03-December 12

Re: How to select range between Begin_date and End_date

Posted 19 April 2019 - 01:26 PM

BETWEEN
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1