CODEWITHSUNDEEP
sqlsql-serverfunctiondatetimeconditional-statements
jadupl
jadupl

Reputation: 210

MSSQL Check If From, To Date will fit between time range

I need a function in MSSQL Server which as parameters will get @startTime @endTime and will check table reservation (which also has startTime and endTime) if asked date range will fit between time range within table and Return 1 If Yes and 0 If no. A little bit confusing I will explain better on table (At least I hope so)

I have table:

|:-----------------------|----------------------:|
| startDate              | endDate               |
|:-----------------------|----------------------:|
| 2017-01-25 00:00:00.000|2017-01-25 12:00:00.000|     
| 2017-01-25 13:00:00.000|2017-01-25 14:00:00.000|  
|:-----------------------|----------------------:|

Need to check If reservation will be avaible for i.e:

@startTime = 2017-01-25 13:30:00.000

@endTime = 2017-01-25 15:00:00.000

And should return 0 because there is reservation in this period of time.

I've tried do this by @startTime > startDate and @endTime < endDate but condition is check for every row and I need check whole table.

Kind Regards

Upvotes: 0

Views: 236

Answers (2)

McNets
McNets

Reputation: 10807

create table info(startdate datetime, enddate datetime);
insert into info values
('2017-01-01', '2017-01-05'),
('2017-01-03', '2017-01-06'),
('2017-01-01', '2017-01-15'),
('2017-01-02', '2017-01-13'),
('2017-01-12', '2017-01-18');

declare @StartDate datetime = '2017-01-03';
declare @EndDate datetime = '2017-01-04'

select *
from info
where startdate <= @StartDate and enddate >= @EndDate;

+----+---------------------+---------------------+
|    |      startdate      |       enddate       |
+----+---------------------+---------------------+
| 1  | 01.01.2017 00:00:00 | 05.01.2017 00:00:00 |
+----+---------------------+---------------------+
| 2  | 03.01.2017 00:00:00 | 06.01.2017 00:00:00 |
+----+---------------------+---------------------+
| 3  | 01.01.2017 00:00:00 | 15.01.2017 00:00:00 |
+----+---------------------+---------------------+
| 4  | 02.01.2017 00:00:00 | 13.01.2017 00:00:00 |
+----+---------------------+---------------------+

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270091

You can do something like this:

select (case when exists (select 1
                          from reservations r
                          where r.startDate <= @endTime and
                                r.endDate >= @startTime
                         )
             then 0 else 1
        end) as available;

The logic is simple. Two time periods overlap if the first starts before the second ends and the first ends after the second starts.

Upvotes: 2

Related Questions