SQL - Select all row that exist between two dates

Question

I have a table like this

employee       | wrok_from     |until
-------------------------------------------
Emp1           | 2017-01-01    |2017-01-15
Emp2           | 2017-01-10    |2017-01-25
Emp3           | 2017-01-20    |2017-02-10
Emp4           | 2017-01-30    |2017-02-20

I want to show every employee that work between two dates. exp: when I select date between 2017-01-23 and 2017-02-20 it will show me :

Emp2           | 2017-01-10    |2017-01-25
Emp3           | 2017-01-20    |2017-02-10
Emp4           | 2017-01-30    |2017-02-20

when I select date between 2017-01-10 and 2017-02-01 it will show me :

Emp1           | 2017-01-01    |2017-01-15
Emp2           | 2017-01-10    |2017-01-25
Emp3           | 2017-01-20    |2017-02-10
Emp4           | 2017-01-30    |2017-02-20

See https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query — Strawberry, Aug 07 '17 at 07:31
I know how to use between. but still I could't find the answer. — Abdussamad Syahrullah Noor, Aug 07 '17 at 07:38
Please, see this Link -> [Answer to your question](https://stackoverflow.com/questions/45460963/months-between-two-dates-in-sql-server-with-starting-and-end-date-of-each-of-the/45462621#45462621) — ARr0w, Aug 07 '17 at 07:45
Event A can be said to overlap Event B if Event A *starts* before Event B *ends*, and *ends* after Event B *starts*. — Strawberry, Aug 07 '17 at 09:59

Imanuel · Accepted Answer · 2017-08-07T10:00:23.613

0

I think this should work. I used :start and :end as placeholders for your input values.

The query selects all employees with their working dates when the working date is at least partially part of your input range.

Visualization (I: input range, y: selected, n: not selected):

--------IIIIIIIIIIIIIIIIIIII----------
--nnn-yyyy---yyyy---------yyyyy----nn-

Query:

SELECT * 
FROM workdates
WHERE     
    work_from < :end AND
    until > :start

edited Aug 07 '17 at 10:00

answered Aug 07 '17 at 07:46

Imanuel

3,596
4
24
46

1

It's really quite simple: Event A can be said to overlap Event B if Event A *starts* before Event B *ends*, and *ends* after Event B *starts*. – Strawberry Aug 07 '17 at 09:57
@Strawberry That's correct and much easier. Thank you for the hint, I changed the answer. – Imanuel Aug 07 '17 at 10:00

SQL - Select all row that exist between two dates

1 Answers1