Barlet Barlet - 2 months ago 26
SQL Question

Searching between available dates in SQL Server

I have a table as the table below.

id accommodation_id room_type_id date_from date_to
3 1 2 2017-08-29 2017-09-10
4 1 9 2017-08-30 2017-09-08


As you can see I have dates from 2017-08-29 to 2017-09-10. Now if I make a search between those dates for example I am searching from 2017-08-25 to 2017-09-15 I should not see any records. I would like to list only if my date is in range between the dates on the table.

Using the query below gets me all the records. How to modify the query to get the result I want?

SELECT id, accommodation_id, room_type_id, date_from, date_to
FROM [Agents].[dbo].[Vacancies]
where (date_from >= '2017-08-25' and date_to <= '2017-09-15')


I hope I am clear with my question!

Answer Source

Change where clause like this.

WHERE 
    ('2017-08-25' between date_from AND date_to ) 
    and ('2017-09-15' between date_from AND date_to )