Discussion :

[dadg]

Bonjour,

j'ai une question un peu bête , mais j'aimerai faire une requette qui me sorte une liste de date entre deux dates. Exemple :

Select date from dual where date > 01.01.2005 and date < 31.12.2005

01.01.2005
02.01.2005
03.01.2005
...
28.12.2005
29.12.2005
30.12.2005
31.12.2005

Simple non ? Mais moi je vois pas comment ! Sauf en créant une table dans la quelle j'insert les dates possible du 01.01.1900 au 31.12.3000 , mais cela me semble un peu bête.

Avez-vous une idée ?

Merci d'avance

[Yorglaa]

à mon avis tu vas devoir passer par du PL/SQL pour faire une boucle...

[McM]

Y'a pas.. En plus DUAL est une table avec 1 seule ligne.
Faut faire une boucle en pl/sql les avoir.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
v_difdate := datefin - datedeb;
FOR i in 0 .. v_difdate
LOOP
  DBMS_OUTPUT.PUT_LINE(datedeb + i);
END LOOP;

[McM]

J'ai une autre idée.. mais pas terrible niveau opti.
Si tu as une table avec plus de lignes que du nombre de jours que tu veux ( 365 par ans pour une période de 100 ans : 36 500 lignes)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
SELECT TRUNC(SYSDATE) + ROWNUM - 1
FROM MATABLE
WHERE ROWNUM <= 10

[plaineR]

Autre solution :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
select trunc(sysdate, 'MONTH') + (rownum - 1) 
from dual 
connect by trunc(sysdate, 'MONTH') + (rownum - 1) <= trunc(last_day(sysdate))

L'exemple ci-dessus te renvoie tous les jours du mois courant, mais ne marche qu'avec 9i (et encore pas avec la 9.2.0.1.0)

Pour les autres versions :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
select trunc(sysdate, 'MONTH') + rownum - 1
from all_objects
where trunc(sysdate, 'MONTH') + (rownum - 1) <= trunc(last_day(sysdate));

A toi d'adapter ces 2 exemples à ton besoin

[EDIT] J'avais pas vu le deuxième post de McM [/EDIT]

[laurentschneider]

all_objects, c'est une vue, pas très rapide, et avec un nombre variable de lignes. Des fois 500 des fois 50000.

la solution CONNECT BY sans PRIOR est illégale. elle n'est pas sensée fonctionnée (boucle dans une hiérarchie) et ne fonctionne en fait que dans quelques versions. Mais c'est une méthode très rapide!

ma préférence est de créer une table CALENDRIER avec tous les jours des 100 dernières et prochaines années... avec un bon index, ce sera très rapide ! et tu peux intégrer ça dans tes requêtes complexes.

Connect by sans prior, si tu essayes de l'intégrer dans une requête déjà exotique à la base, tu risques d'avoir de mauvaises surprises.

A bon entendeur...

[plaineR]

all_objects, c'est une vue, pas très rapide, et avec un nombre variable de lignes. Des fois 500 des fois 50000.

Ce n'était évidemment qu'un exemple (trouvé il y a longtemps sur le site d'oracle...). Après, tout dépend du contexte... Si la requête sert une fois à remplir une table elle ne me semble pas mal, par contre c'est vrai que si elle sert dans un contexte où la performance est importante, créer une table calendrier est mieux... Mais la question du post était : comment obtenir une liste de dates, sans plus de précision.

ma préférence est de créer une table CALENDRIER avec tous les jours des 100 dernières et prochaines années... avec un bon index, ce sera très rapide ! et tu peux intégrer ça dans tes requêtes complexes.

Et comment tu la remplis ta table ? Moi je l'ai remplie avec la requête précisée dans mon post précédent...

Enfin pour ce qui est de la solution du connect by, l'exemple est tiré du site http://asktom.oracle.com, et il est précisé que cela fonctionne pour les versions 9ir2 et supérieure.

[dadg]

Merci à tous pour vos nombreuses réponses, en fait c'est pour générer un calendrier avec des jours fériés. J'ai un table qui contient des jours fériés et je veux pouvoir afficher dans un intervalle de deux date les jours et donner la description du jour férié ci il fait partie de ma table.

Après quelque test, je pense effectivement que la meilleures solutions serai de faire une table calendier. Car au niveau perf, c'est pas super (peut-être aussi ma machin ).

Par contre lorsque je fais ma requette avec l'exemple :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
select trunc(sysdate, 'MONTH') + rownum - 1 
from all_objects 
where trunc(sysdate, 'MONTH') + (rownum - 1) <= trunc(last_day(sysdate));

j'ai remarqué que la date est en fait le 01.01.0006 et non pas 2006 :, mais j'ai pas très très bien compris le fonctionnement de la requêtte il faut dire. Au niveau de l'affichage c'est OK, mais en passant par to_date cela me dit 0006.

Par contre je ne peux pas vraiement remplir ma table avec, puisque ma table calendrier contient un type date ?

Je vais encore chercher. En tous les cas, merci pour vos infos.

[plaineR]

Au niveau de l'affichage c'est OK, mais en passant par to_date cela me dit 0006.

Pourquoi faire un to_date, le résultat de la requête est une date.

Par contre je ne peux pas vraiement remplir ma table avec, puisque ma table calendrier contient un type date ?

Mais si, le résultat de la requête est une date :
trunc (sysdate, 'MONTH') => renvoie une date tronquée au premier jour du mois

[dadg]

C'est juste, je viens de finir de remplir ma table pour 01.01.1950 à 31.12.2050 et cela n'a possé aucun problème au niveau de l'insert et les données sont correcte. Je me possais juste la question pour en passant par to_date cela me donnais une date en 0006.

Du coups, j'ai une question au niveau du stockage de la date par Oracle (version 8i). Une date peu être stocker soit avec ou sans heure, ou l'heure est implicite si on ne la spécifie pas ?

[Yanika_bzh]

C'est juste, je viens de finir de remplir ma table pour 01.01.1950 à 31.12.2050 et cela n'a possé aucun problème au niveau de l'insert et les données sont correcte. Je me possais juste la question pour en passant par to_date cela me donnais une date en 0006.

Du coups, j'ai une question au niveau du stockage de la date par Oracle (version 8i). Une date peu être stocker soit avec ou sans heure, ou l'heure est implicite si on ne la spécifie pas ?

Le type DATE n'est pas une simple chaine de caracteres. C'est pour cela qu'il faut utiliser des fonctions de conversion lors des manipulations.
Apres, c'est toi qui decide ce que tu veux afficher.

[plaineR]

Si tu ne spécifies pas d'heure oracle la met à 0 :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
SQL> create table test_date (coldate date);
Table created.
 
SQL> insert into test_date values (to_date ('02/03/2006', 'DD/MM/RRRR'));
1 row created.
 
SQL> select to_char (coldate, 'DD/MM/RRRR HH24:MI:SS') from test_date;
TO_CHAR(COLDATE,'DD
-------------------
02/03/2006 00:00:00

[dadg]

OK,

super, c'est bon et rapide effectivement et d'ici qu'on me demande des dates du type 12.04.2456, j'aurrai passé le relai en ce qui concerne mon prog.

Merci à tous pour vos réponses et éclairages.

[McM]

Si c'est bon, Cliques sur

[dadg]

OK, sorry oublié

[laurentschneider]

Enfin pour ce qui est de la solution du connect by, l'exemple est tiré du site http://asktom.oracle.com, et il est précisé que cela fonctionne pour les versions 9ir2 et supérieure.

si te cherches sur le site de tom, tu pourras lire mes commentaires :-)

c'est spécifié dans la doc, sql reference
PRIOR

In a hierarchical query, one expression in the CONNECT BY condition must be qualified by the PRIOR operator

quant à ta requête, juste pour ton information :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
 
SQL> select * from v$version;
 
BANNER
----------------------------------------------------------------
Oracle7 Server Release 7.3.4.5.0 - Production
 
SQL> select trunc(sysdate, 'MONTH') + (rownum - 1)
  2  from dual
  3  connect by trunc(sysdate, 'MONTH') + (rownum - 1) <= trunc(last_day(sysdate));
ERROR:
ORA-01436: CONNECT BY loop in user data
 
SQL> select version from v$instance;
8.1.7.4.0
SQL> select trunc(sysdate, 'MONTH') + (rownum - 1)
  2  from dual
  3  connect by trunc(sysdate, 'MONTH') + (rownum - 1) <= trunc(last_day(sysdate));
01.03.2006 00:00:00
 
SQL> select version from v$instance;
9.2.0.7.0
SQL> select trunc(sysdate, 'MONTH') + (rownum - 1)
  2  from dual
  3  connect by trunc(sysdate, 'MONTH') + (rownum - 1) <= trunc(last_day(sysdate));
01.03.2006 00:00:00
02.03.2006 00:00:00
03.03.2006 00:00:00
04.03.2006 00:00:00
05.03.2006 00:00:00
06.03.2006 00:00:00
07.03.2006 00:00:00
08.03.2006 00:00:00
09.03.2006 00:00:00
10.03.2006 00:00:00
11.03.2006 00:00:00
12.03.2006 00:00:00
13.03.2006 00:00:00
14.03.2006 00:00:00
15.03.2006 00:00:00
16.03.2006 00:00:00
17.03.2006 00:00:00
18.03.2006 00:00:00
19.03.2006 00:00:00
20.03.2006 00:00:00
21.03.2006 00:00:00
22.03.2006 00:00:00
23.03.2006 00:00:00
24.03.2006 00:00:00
25.03.2006 00:00:00
26.03.2006 00:00:00
27.03.2006 00:00:00
28.03.2006 00:00:00
29.03.2006 00:00:00
30.03.2006 00:00:00
31.03.2006 00:00:00
 
SQL> select version from v$instance;
10.1.0.5.0
SQL> select trunc(sysdate, 'MONTH') + (rownum - 1)
  2  from dual
  3  connect by trunc(sysdate, 'MONTH') + (rownum - 1) <= trunc(last_day(sysdate));
01.03.2006 00:00:00
02.03.2006 00:00:00
03.03.2006 00:00:00
04.03.2006 00:00:00
05.03.2006 00:00:00
06.03.2006 00:00:00
07.03.2006 00:00:00
08.03.2006 00:00:00
09.03.2006 00:00:00
10.03.2006 00:00:00
11.03.2006 00:00:00
12.03.2006 00:00:00
13.03.2006 00:00:00
14.03.2006 00:00:00
15.03.2006 00:00:00
16.03.2006 00:00:00
17.03.2006 00:00:00
18.03.2006 00:00:00
19.03.2006 00:00:00
20.03.2006 00:00:00
21.03.2006 00:00:00
22.03.2006 00:00:00
23.03.2006 00:00:00
24.03.2006 00:00:00
25.03.2006 00:00:00
26.03.2006 00:00:00
27.03.2006 00:00:00
28.03.2006 00:00:00
29.03.2006 00:00:00
30.03.2006 00:00:00
31.03.2006 00:00:00
01.04.2006 00:00:00 ***********
 
SQL> select version from v$instance;
10.2.0.1.0
SQL> select trunc(sysdate, 'MONTH') + (rownum - 1)
  2  from dual
  3  connect by trunc(sysdate, 'MONTH') + (rownum - 1) <= trunc(last_day(sysdate));
01.03.2006 00:00:00
02.03.2006 00:00:00
03.03.2006 00:00:00
04.03.2006 00:00:00
05.03.2006 00:00:00
06.03.2006 00:00:00
07.03.2006 00:00:00
08.03.2006 00:00:00
09.03.2006 00:00:00
10.03.2006 00:00:00
11.03.2006 00:00:00
12.03.2006 00:00:00
13.03.2006 00:00:00
14.03.2006 00:00:00
15.03.2006 00:00:00
16.03.2006 00:00:00
17.03.2006 00:00:00
18.03.2006 00:00:00
19.03.2006 00:00:00
20.03.2006 00:00:00
21.03.2006 00:00:00
22.03.2006 00:00:00
23.03.2006 00:00:00
24.03.2006 00:00:00
25.03.2006 00:00:00
26.03.2006 00:00:00
27.03.2006 00:00:00
28.03.2006 00:00:00
29.03.2006 00:00:00
30.03.2006 00:00:00
31.03.2006 00:00:00

à mon avis, Oracle7 reporte une erreur comme attendu !

Tom Kyte employe le générateur de lignes "CONNECT BY ". Cependant beaucoup de lecteurs de Tom ne se rendent pas compte qu'il s'agit d'un Hack et que les résultats ne seront pas forcément ceux escomptés

[plaineR]

Ok, merci pour ces précisions.

[TForm1]

Bonjour, je remet ce sujet au gout du jour pour proposer une reponse avec la Table Dual

select * from (select level + to_date('01/08/2008','dd/mm/yyyy') - 1 from dual
connect by level < (to_date('31/08/2008','dd/mm/yyyy') - to_date('01/08/2008','dd/mm/yyyy') + 2)) ;

[TForm1]

Bonjour, je remet ce sujet au gout du jour pour proposer une reponse avec la Table Dual

select * from (select level + to_date('01/08/2008','dd/mm/yyyy') - 1 from dual
connect by level < (to_date('31/08/2008','dd/mm/yyyy') - to_date('01/08/2008','dd/mm/yyyy') + 2)) ;

Ouah Tform1, c'est de la bombe ton truc, Merci