1

I have a table name as 'records' field is 'dates', how to get missing dates in a table based on passing two dates like 2018-03-23,2018-03-30 using MySQL query

records :

id  dates
------------
1   2018-03-23
2   2018-03-24
3   2018-03-27
4   2018-03-28
5   2018-03-30

Expected Result is missing dates: 25,26,29

Lithilion
  • 1,097
  • 2
  • 11
  • 26
Srinu Chilukuri
  • 83
  • 2
  • 11

2 Answers2

0

Please check the example below:

CREATE TABLE #tmp (
    my_date DATETIME
)

INSERT INTO #tmp (my_date) VALUES ('2016-01-01')
INSERT INTO #tmp (my_date) VALUES ('2016-01-02')
INSERT INTO #tmp (my_date) VALUES ('2016-01-04')
INSERT INTO #tmp (my_date) VALUES ('2016-01-05')
INSERT INTO #tmp (my_date) VALUES ('2016-01-07')
INSERT INTO #tmp (my_date) VALUES ('2016-01-08')
INSERT INTO #tmp (my_date) VALUES ('2016-01-10')
INSERT INTO #tmp (my_date) VALUES ('2016-01-11')

DECLARE @max_date DATETIME
SELECT @max_date = max(my_date) FROM #tmp

SELECT DATEADD(day,1,t1.my_date) as miss_date
FROM #tmp t1 
LEFT JOIN #tmp t2 ON t1.my_date=DATEADD(day,-1,t2.my_date)
WHERE t2.my_date is null 
  AND t1.my_date<>@max_date

Also, please see: MySQL: Find Missing Dates Between a Date Range

Eray Balkanli
  • 7,752
  • 11
  • 48
  • 82
0

Here's a SQL Server Solution 

    declare @date datetime=(Select min(date) from table)
    declare @maxdate datetime = (Select max(date) from table)

    while @date<=@maxdate
    begin
    if @date not in (Select date from table)
    select @date

    select @date=dateadd(dd, 1, @date)
    end
Daniel Marcus
  • 2,686
  • 1
  • 7
  • 13
  • This will loop through all the dates and select the ones not in your table. If you need them in a list, write each date to a temp table as you loop through and select from the temp table at the end. – Daniel Marcus Mar 27 '18 at 15:10