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 !

I don't think so, Oracle will help you to find the missing dates. Since, these are data, so all you can do, write some query, which will help you to find those missing date — Ravi
– Ravi, Commented Dec 21, 2012 at 11:48

Peter Lang · Accepted Answer · 2017-07-17 18:16:59Z

8

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.

edited Jul 17, 2017 at 18:16

answered Dec 21, 2012 at 11:54

Peter Lang

55.7k29 gold badges153 silver badges165 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

SimonH Over a year ago

is there a possibilty to set an end date instead of the number of days? e.g. when selecting dates from 01.01.2012 to 31.12.2014 2012 will have 366 days but 2013 and 2014 only have 265 days as 2012 was a leap year.

Peter Lang Over a year ago

@LPrc: I edited my answer to work for each year, no matter if leap year or not. Actually makes more sense like that, hope it helps.

Hamlet Hakobyan · Accepted Answer · 2012-12-21 11:50:01Z

1

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

answered Dec 21, 2012 at 11:50

Hamlet Hakobyan

33.5k6 gold badges55 silver badges70 bronze badges

Comments

MontyPython · Accepted Answer · 2015-01-31 10:19:33Z

0

Almost the same answer as @Peter, but a very slightly different version.

select all_dates.date_ missing_dates from 

(select to_date('01-Jan-14') + level - 1 date_
from dual connect by level <= 365) all_dates 

left join

((select to_date('01-Jan-14') + level - 1 date_
from dual connect by level <= 365)               

minus

(select to_date(round (dbms_random.value (1, 28))
        || '-'
        || round (dbms_random.value (01, 12))
        || '-'
        || round (dbms_random.value (2014, 2014)),
        'DD-MM-YYYY') + level - 1 random_date_1 
from dual
connect by level <= 52)) transaction_data

on all_dates.date_ = transaction_data.date_
where transaction_data.date_ is null;

answered Jan 31, 2015 at 10:19

MontyPython

3,03412 gold badges40 silver badges59 bronze badges

Collectives™ on Stack Overflow

Find missing dates using SQL

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related