Find missing dates using SQL

Question

I have some dates in table over of a two years like this as a example date

01-jan-2012
02-jan-2012
04-jan-2012
05-jan-2012
06-jan-2012
07-jan-2012
09-jan-2012
11-jan-2012
.
.
.
01-DEC-2012

I think you have noticed that there a missing date of 03-jan-2012 and 08-jan-2012 and the same criteria with all dates.My Question is that is there any way in oracle to find the missing dates.Plz Help !

Answer 1

This will get you all missing days for one year (SQL Fiddle).

all_dates generates a list of all dates of 2012 (adjust as required), and the LEFT JOIN checking for IS NULL eliminates those dates that exist in your source-table.

WITH all_dates AS (
  SELECT TO_DATE('01-jan-2012') + ROWNUM - 1 AS d
  FROM dual
  CONNECT BY ROWNUM <= ADD_MONTHS(TO_DATE('01-jan-2012'), 12 ) - TO_DATE('01-jan-2012')
)
SELECT all_dates.d
FROM all_dates
LEFT JOIN t ON ( t.d = all_dates.d )
WHERE t.d IS NULL
ORDER BY all_dates.d
;

Make sure to use a bind variable instead of hard-coding the date three times.

Answer 2

You can generate sequence of date range you needed, then use LEFT JOIN to find missed dates.