Display dates in between dates in SQL

Question

I have a simple table below with 5 records.

Id:    Date
123    9/1/15
123
123
123
123    9/5/15

How do I write in SQL server to display the missing dates from 9/2/15 to 9/4/15?

I was thinking about using a loop but my skills are not there yet. Basically, I like to know how to write a SQL script that displays the missing dates.

possible duplicate of [Getting Dates between a range of dates](http://stackoverflow.com/questions/271595/getting-dates-between-a-range-of-dates) — Rubens Farias, Sep 15 '15 at 18:16
This is a use case for a calendar table, plenty of good articles on that, and an outer join to that. A loop will work, but won't perform nearly as well as a table that contains a list of dates. — Hart CO, Sep 15 '15 at 18:19
Hi Ruben, I look into those but not exactly what I was looking for. Thanks. — joe, Sep 15 '15 at 18:36
if the dates are missing, how u figure it out the date? column can either have a date or not.. and u want to search for date that is missing and the date between 9/2 and 9/4??? aren't they mutually exclusive? — g2000, Sep 15 '15 at 19:03
Hi Guys, Thanks for your feedback and assistance. I have not got a chance to review your help since I asked the question. Thanks a gain, forks! Joe. — joe, Sep 18 '15 at 11:55

score 1 · Answer 1 · edited May 23 '17 at 12:06

I am guessing you want something like this:

WITH T(ID, Date, MaxDate) AS (
    SELECT ID, MIN(Date), MAX(Date) FROM MyTable GROUP BY ID
    UNION ALL
    SELECT ID, DATEADD(day, 1, Date), MaxDate FROM T WHERE Date < MaxDate 
)
SELECT ID, Date FROM T ORDER BY ID, Date

Please follow this fiddle to see it in action: http://sqlfiddle.com/#!6/682180/2/0

Credit: my query is largely based on this answer (proposed by Rubens): Getting Dates between a range of dates

I must say that I don't like the idea of the database layer being responsible for representation of data. Please consider doing a repair on the table itself, filling in the missing dates. Though I have no idea how; there seems to be no unique identifier to tell the three NULL records apart. You may want to take this back to the source of the data; looks like you have been importing an Excel sheet. Try to repair it there.

sadmicrowave · Answer 2 · 2015-09-15T19:24:31.583

What about something like this?

DECLARE @TMP TABLE ( ID INT, ADMIT DATE )

INSERT INTO @TMP ( ID, ADMIT )
VALUES   ( 123, '2015-09-01' )
        ,( 123, NULL )
        ,( 123, NULL )
        ,( 123, NULL )
        ,( 123, '2015-09-05' )

SELECT TMP.ID
      ,CASE 
          WHEN TMP.ADMIT IS NULL
             THEN DATEADD(D,(ROW_NUMBER() OVER (PARTITION BY TMP.ID, TMP.ADMIT ORDER BY TMP.ID ASC)),B.MIN_ADMIT)
          ELSE TMP.ADMIT 
       END  AS NEW_DATE
FROM @TMP AS TMP
      INNER JOIN ( SELECT ID, MIN(ADMIT) AS MIN_ADMIT
                   FROM @TMP
                   GROUP BY ID
                 ) B ON B.ID = TMP.ID
ORDER BY TMP.ID, NEW_DATE ASC

Basically, this just creates a temp table with your dummy data so we can test with some values. Then performs the query that uses a row ranking for each row number and add's that value to the minimum date found in the original list...

We need the INNER JOIN because we need to do a GROUP BY clause to get the MIN date from your original table. We can't do a GROUP BY in our original SELECT because that will begin excluding some rows as we only have ID and ADMIT to group off of; therefore the 3 records will NULL would be truncated to 1 record.

score 0 · Answer 3 · answered Sep 15 '15 at 19:34

try using MINUS - demonstrated below for numbers, you can use it for dates. MINUS should make the query maintainable and it will simply the query.

SQL Fiddle

Query 1:

select n from
 ( select rownum n from dual connect by level <= 5)
 where n >= 1
 MINUS
 select n from
 ( select rownum n from dual connect by level <= 2)
 where n >= 1

Results: | N | |---| | 3 | | 4 | | 5 |

Hope it will help you :)

Display dates in between dates in SQL

3 Answers3