show missing dates

Question

hello,
I have a table similar to below screen-shot which shows daily dates, year, month, day.
I am populating it daily.
Question:
As you see there are gaps where there are missing rows for some of the days.
How can I return the missing dates?
for example for the example shown here, I would like to return, something like:

year month days

--------------------

2022 06 02
2022 06 03
...
2022 06 17

thank you

Answer 1

Hi Arkiboys,

The script below creates a date table with you can use to check your missing dates. I created the #temp table to represent your table.

DECLARE @Startdate date = CAST('20220101' as datetime)  
DECLARE @Enddate date = CAST('20220110' as datetime)  
  
DROP TABLE IF EXISTS #temp  
CREATE TABLE #temp  
(  
_year int,  
_month int,  
_day int  
)  
  
INSERT INTO #temp VALUES (2022,1,1)  
INSERT INTO #temp VALUES (2022,1,2)  
INSERT INTO #temp VALUES (2022,1,4)  
INSERT INTO #temp VALUES (2022,1,5)  
INSERT INTO #temp VALUES (2022,1,6)  
INSERT INTO #temp VALUES (2022,1,9)  
INSERT INTO #temp VALUES (2022,1,10)  
  
  
;WITH DateTable  
AS  
(  
  SELECT @Startdate AS [date]  
  UNION ALL  
  SELECT DATEADD(dd, 1, [date])  
  FROM DateTable  
  WHERE DATEADD(dd, 1, [date]) <= @Enddate  
)  
  
  
SELECT [date] FROM DateTable   
EXCEPT  
select  datefromparts(_year, _month, _day) FROM #temp  
OPTION (MAXRECURSION 0)  

Answer 2

Hi @arkiboys
Try this code:

DECLARE @StartDate  DATE = '2022-05-28';  
DECLARE @CutoffDate DATE = '2022-06-20'  
  
;WITH seq(n) AS   
(  
  SELECT 0   
  UNION ALL   
  SELECT n + 1 FROM seq  
  WHERE n < DATEDIFF(DAY, @StartDate, @CutoffDate)  
),d(d) AS   
(  
  SELECT DATEADD(DAY, n, @StartDate) FROM seq  
),src AS  
(  
  SELECT  
    TheDate   = CONVERT(date, d),  
    TheDay    = DATEPART(DAY, d),  
    TheMonth  = DATEPART(MONTH, d),  
    TheYear   = DATEPART(YEAR, d)  
  FROM d  
)  
SELECT S.TheYear,S.TheMonth,S.TheDay   
FROM src S LEFT JOIN Your_Table T ON S.TheYear=T._Year AND S.TheMonth=T._Month AND S.TheDay=T._Day  
WHERE T._Year IS NULL  
ORDER BY TheDate DESC  

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.