How to find missing date between two dates in sql table.


I would like to know a sql query to find all missing dates between two dates , suppose my sql table is as below

ID

Date

1

2018-1-10

2

2018-1-12

3

2018-1-13

4

2018-1-15

5

2018-1-17

6

2018-1-18

7

2018-1-20

8

2018-1-21

 

I need to run sql query which will return missing dates from date column like  ‘2018-1-11’ , ‘2018-1-14’.

 


Asked by:- RameshwarLate
0
: 214 At:- 12/11/2018 9:08:41 AM
sql date sql-server






1 Answers
profileImage Answered by:- Sam

You can use the EXCEPT operator to compare a set of continuous dates to the dates with gaps.

Suppose my table is like below

find-missing-date-between-two-dates-in-sql-server-min.png

Then you can use the below SQL command to get the list of missing dates between two dates

DECLARE  @MaxDate DATE, 
         @MinDate DATE, 
         @iDate  DATE 
-- SQL Server table variable 
DECLARE  @DateSequence TABLE( 
                          DATE DATE 
                          ) 
SELECT @MaxDate = Convert(DATE,Max(Dates)), 
       @MinDate = Convert(DATE,Min(Dates)) 
FROM   [Students].[dbo].[DatesTable]

SET @iDate = @MinDate 

WHILE (@iDate <= @MaxDate) 
  BEGIN 
    INSERT @DateSequence
    SELECT @iDate 
     
    SET @iDate = Convert(DATE,Dateadd(DAY,1,@iDate)) 
  END 

SELECT Gaps = DATE 
FROM   @DateSequence
EXCEPT 
SELECT DISTINCT Convert(DATE,Dates) 
FROM   [Students].[dbo].[DatesTable]
GO 

Output:

sql-server-missing-dates-between-two-dates-output-min.png

hope it helps.

2
At:- 12/13/2018 4:45:11 PM
Excellent answer with proper example. 0
By : vikas_jk - at :- 12/15/2018 12:26:56 PM





Login/Register to answer
Or
Register directly by posting answer/details

Full Name *

Email *




By posting your answer you agree on privacy policy & terms of use