Datums Genereren Met Oracle SQL

Uitgangspunt

Hoe genereer ik met SQL makkelijk alle dagen van een jaar?

Uitleg

Daarvoor kun je een cross join gebruiken tussen twee simpele tabellen: een tabel dagen met alle dagen van een maand (1 t/m 31) en een tabel maanden met alle maanden van een jaar (1 t/m 12):

select d.dag||’-‘||m.maand||’-2010′

from dagen d cross join maanden m
order by m.maand, d.dag;

D.DAG||'-'||M.MAAND||'-2010'
-------------------------------------
1-1-2010
2-1-2010
3-1-2010
4-1-2010
5-1-2010
6-1-2010
7-1-2010
...
31-12-2010

In dit geval heeft Oracle alle mogelijke combinaties gemaakt van dagen en maanden, en heeft zo 12 x 31 = 372 rijen gemaakt.

Dit is natuurlijk meer dan we willen: ook foutieve datums zoals 31-2-2010 zijn gegenereerd. Als we hier echte datums van genereren krijgen we de volgende foutmelding:

select to_date(d.dag||'-'||m.maand||'-2010','dd-mm-yyyy')
from dagen d cross join maanden m
order by m.maand, d.dag

SQL Error: ORA-01839: date not valid for month specified
01839. 00000 -  "date not valid for month specified"
*Cause:
*Action:

Met een truc kunnen we de foutieve datums hieruit weg filteren.

In de volgende WHERE clausule geven we aan dat alleen de dagen kleiner of gelijk aan de laatste dag van de maand mee mogen doen. Dus als het maandnummer 4 is, dan doen alleen dagen kleiner of gelijk aan 30 mee.

select to_date(d.dag||'-'||m.maand||'-2010','dd-mm-yyyy') as "DATE"
from dagen d cross join maanden m
where d.dag<=to_char(last_day(to_date('1-'||m.maand||'-2010','dd-mm-yyyy')),'dd')
order by 1;

Hier hebben we de functie last_day gebruikt met als invoer de eerste dag van de maand (van elke maand bestaat in ieder geval dag 1).

Als uitvoer geeft deze functie de laatste dag van de maand.

Meer informatie? Neem contact op met ons!