Find first available date, given a date range in SQL

Question

Suppose you have a table with date ranges for which those dates are NOT available.

+------------+------------+
| StartDate  | EndDate    |
+------------+------------+
| 2014-10-1  | 2014-10-15 |
+------------+------------+
| 2014-11-4  | 2014-11-28 |
+------------+------------+
| 2014-12-17 | NULL       |
+------------+------------+

An EndDate of NULL means till the end of time. So there are no available dates after 12/17.

Given a date range, I need to find the first available date. I'm not that great at SQL and I can't think of how this could be done.

Examples: Given the desired date range

2014-10-13 to 2014-11-17, the query should return 2014-10-16

2014-10-13 to 2014-11-30, the query should return 2014-10-16

2014-10-21 to 2014-11-30, the query should return 2014-10-21

2014-12-01 to 2015-1-13, the query should return 2014-12-01

I'd appreciate any help. Thanks.

possible duplicate of [Find closest date in SQL Server](http://stackoverflow.com/questions/14023145/find-closest-date-in-sql-server) — Jonast92, Oct 29 '14 at 22:46
Just questioning because of the date format, is this really Microsoft SQL Server? Or MySQL? — Brian DeMilia, Oct 29 '14 at 23:06
@Brian - Nothing wrong with that date format for MS SQL Server — JohnS, Oct 30 '14 at 21:23

JohnS · Accepted Answer · 2014-10-30T21:20:56.250

Try this, no loops! (BTW your last test case is was wrong I believe)

CREATE TABLE [dbo].[Dates]([StartDate] [datetime] NULL, [EndDate] [datetime] NULL ) ON [PRIMARY]

INSERT INTO [dbo].[Dates]([StartDate], [EndDate])
SELECT '20141001 00:00:00.000', '20141015 00:00:00.000' UNION ALL
SELECT '20141104 00:00:00.000', '20141128 00:00:00.000' UNION ALL
SELECT '20141217 00:00:00.000', NULL

DECLARE @Date1 DATETIME;
DECLARE @Date2 DATETIME;

-- Test case 1
SET @Date1 = '2014-10-13';
SET @Date2 = '2014-11-17';

-- Test case 2
SET @Date1 = '2014-10-13';
SET @Date2 = '2014-11-30';

-- Test case 3
SET @Date1 = '2014-10-21';
SET @Date2 = '2014-11-30';

-- Test case 4
SET @Date1 = '2014-12-01';
SET @Date2 = '2015-01-13';

-- Generate a temp table of dates from @Date1 to @Date2
SELECT TOP (DATEDIFF(DAY, @Date1, @Date2)+1)
  [date] = DATEADD(day, ROW_NUMBER() OVER (ORDER BY [object_id]) - 1, @Date1)
INTO
  #DateRange
FROM
  sys.all_objects

-- Remove non-available dates
DELETE #DateRange  
WHERE  
  EXISTS(SELECT 1 FROM dbo.Dates WHERE date BETWEEN StartDate AND ISNULL(EndDate, date))

-- Select first available date  
SELECT TOP 1 * FROM #DateRange AS dr ORDER BY date

Find first available date, given a date range in SQL

1 Answers1

Linked