Query to find missing dates between two date range in a table. We need to LEFT JOIN the table.
WITH all_dates AS (
SELECT TO_DATE('01-jan-09') -- Start-date
+ ROWNUM - 1 AS d
FROM dual
CONNECT BY ROWNUM <= to_number(to_char(to_date('31-dec-16'),'J')-to_char(to_date('01-jan-09'),'J')) + 1 -- Number of days calculated using Julian numbers
)
SELECT all_dates.d
FROM all_dates
LEFT JOIN t ON ( t.day = all_dates.d )
WHERE t.day IS NULL
ORDER BY all_dates.d;
