Discussion :

[looping]

Bonjour,


En partant de la requête suivante qui à 2 paramètres date (debut et fin) et qui va bien

Code :

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

1
2
3
4
5
6
7
 
select sum(L_BLIV.LBQTE * L_CMDCLI.LCPRIX)as TOTAL from FACTURE
join L_FACTURE on (FACTURE.FAIDFACT = L_FACTURE.LFIDFACT)
join L_BLIV on (L_FACTURE.LFIDLBL = L_BLIV.LBIDLBL)
join L_CMDCLI on (L_BLIV.LBLIDLCDE = L_CMDCLI.LCIDLCDE)
join CMDCLI on (L_CMDCLI.LCIDCDE = CMDCLI.COIDCDE)
where FACTURE.FADATFACT between :debut and :fin

je voudrais faire la même chose , mais en passant seulement l'année comme paramètre et en gardant mois et jour fixe .
Par exemple pour une requête du 1 janvier au 31 mars

Code :

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

1
2
 
where FACTURE.FADATFACT between cast ( :annee || '/01/' || '01' as date) and cast ( :annee || '/03/' || '31' as date)

mais ça ne fonctionne pas ?

Est ce que c'est possible ?

cordialement

[SergioMaster]

Bonjour,

AMHA c'est le formatage de la date qui n'est pas bon à cause des /

formats possibles
en gardant la forme AN MOIS JOUR il faudrait écrire ainsi

Code SQL :

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

where FACTURE.FADATFACT between cast ( :annee || '-01-01' as date) and cast ( :annee || '-03-31' as date)

N.B. Utiliser des Alias pour les tables permettrait une lecture (et écriture ) plus facile

[foucoudav]

Une autre manière de l'écrire :

Code :

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

1
2
 
where FACTURE.FADATFACT between date :annee || '-01-01'  and date  :annee || '-03-31'

[looping]

Bonjour,

j'ai essayé mais cela ne fonctionne pas.

La requête de Sergiomaster me donne "Data type unknown."
Celle de Foucoudav : Token unknown - line 6, column 38.


Cordialement

[SergioMaster]

Bonjour,
A mon avis il faut passer le paramètre en tant que string et avec des quotes (quotedstr)
mais le meiux resterai quand même le premier SQL et de passer donc deux dates (calculées avant, dans le programme)

[alanglet]

Bonjour,

Il faudrait d'abord savoir quel est le format de FACTURE.FADATFACT Est-ce DATE, TIMESTAMP ou un VARCHAR?

André

[Artemus24]

Salut looping.

Vous êtes dans quelle version de FireBird ?

where FACTURE.FADATFACT between cast ( :annee || '/01/' || '01' as date) and cast ( :annee || '/03/' || '31' as date)

C'est une très mauvaise idée de mettre votre date en dure de cette façon là.

FireBird va faire d'une part la concatenation et d'autre part la convertion d'une manière implicite de la date dans le bon type afin de faire la comparaison.
C'est une perte de temps inutile qui va pénaliser la performance de votre requête.

Le mieux est de mettre la date au bon format et au bon type avant de faire le passage des paramètres.

Il serait aussi intéressant de connaitre le type de la colonne "FACTURE.FADATFACT" !
Encore que cela ne soit pas trop utile, comme on peut le voir dans cet exemple ci-après :

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
CREATE DATABASE '..\..\Data\Base.fdb' page_size 4096 DEFAULT CHARACTER SET WIN1252;
 
-- =====================
-- Création table 'test'
-- =====================
 
CREATE TABLE test (
id       integer    not null,
date_1   date       not null,
date_2   timestamp  not null,
date_3   char(10)   not null);
 
-- ==============
-- Initialisation
-- ==============
 
INSERT INTO test VALUES (1, '2017/01/29', '2017/01/29', '2017/01/29');
INSERT INTO test VALUES (2, '2017/02/01', '2017/02/01', '2017/02/01');
 
-- ==============
-- Initialisation
-- ==============
 
select * from test;
 
          ID      DATE_1                    DATE_2 DATE_3
============ =========== ========================= ==========
           1 2017-01-29  2017-01-29 00:00:00.0000  2017/01/29
           2 2017-02-01  2017-02-01 00:00:00.0000  2017/02/01
 
 
-- =========
-- exemple 1
-- =========
 
select * from test where date_1 between cast('2017' || '-01-01' as date) and cast('2017' || '-01-31' as date);
 
          ID      DATE_1                    DATE_2 DATE_3
============ =========== ========================= ==========
           1 2017-01-29  2017-01-29 00:00:00.0000  2017/01/29
 
select * from test where date_2 between cast('2017' || '-01-01' as date) and cast('2017' || '-01-31' as date);
 
          ID      DATE_1                    DATE_2 DATE_3
============ =========== ========================= ==========
           1 2017-01-29  2017-01-29 00:00:00.0000  2017/01/29
 
select * from test where date_3 between cast('2017' || '-01-01' as date) and cast('2017' || '-01-31' as date);
 
          ID      DATE_1                    DATE_2 DATE_3
============ =========== ========================= ==========
           1 2017-01-29  2017-01-29 00:00:00.0000  2017/01/29
 
 
-- =========
-- exemple 2
-- =========
 
select * from test where date_1 between cast('2017' || '/01/01' as date) and cast('2017' || '/01/31' as date);
 
          ID      DATE_1                    DATE_2 DATE_3
============ =========== ========================= ==========
           1 2017-01-29  2017-01-29 00:00:00.0000  2017/01/29
 
select * from test where date_2 between cast('2017' || '/01/01' as date) and cast('2017' || '/01/31' as date);
 
          ID      DATE_1                    DATE_2 DATE_3
============ =========== ========================= ==========
           1 2017-01-29  2017-01-29 00:00:00.0000  2017/01/29
 
select * from test where date_3 between cast('2017' || '/01/01' as date) and cast('2017' || '/01/31' as date);
 
          ID      DATE_1                    DATE_2 DATE_3
============ =========== ========================= ==========
           1 2017-01-29  2017-01-29 00:00:00.0000  2017/01/29
 
 
exit;
 
Appuyez sur une touche pour continuer...

Pour ce test, j'utilise la version FireBird 3.0.1.
Comme on peut le voir, votre écriture est correcte et ne pose aucun problème.
L'erreur est peut-être dans le type utilisé pour le paramètre année.

@+

[alanglet]

Bonsoir,

Il serait aussi intéressant de connaitre le type de la colonne "FACTURE.FADATFACT" !
Encore que cela ne soit pas trop utile, comme on peut le voir dans cet exemple ci-après :

Votre exemple ne fait que mettre en évidence que Firebird sait transcrire dans le type date ou timestamp une date saisie sous une forme littérale. La doc l'explique très bien https://firebirdsql.org/en/firebird-date-literals/
Si la date est simplement mémorisée dans un varchar, elle peut l'être sous une forme quelconque qui n'est pas AAAA/MM/JJ mais peut-être JJ MM AAAA ou autre. Mais cela seul looping peut nous le dire.

André

[Artemus24]

Salut alanglet.

Comme on le constate dans mon exemple, FireBird fait des conversions implicites. Je parle bien sûr des dates dans l'insert.
C'est pourquoi l'exemple donné par Looping, bien que syntaxiquement correcte, est à déconseiller pour des raisons de performances.

Le type de la colonne "FACTURE.FADATFACT" est d'une grande importance.
Car rien n'indique que la conversion explicite dans le type date, soit justement le bon type de la comparaison de la clause where.

En effet, comme vous le dite, il se peut que cela soit le type varchar, avec un autre format de représentation de la date.

@+

[SergioMaster]

Bonjour,

je ne m'attarde pas sur le type de la colonne "FACTURE.FADATFACT" à priori Date ou TimeStamp
ce que ne nous dit pas looping est aussi dans quel contexte est utilisée cette requête, dans un environnement FIREBIRD donc au sein d'une procédure, ou à l'intérieur d'un programme (i.e. Delphi) hypothèse de ma seconde réponse (à cause de La requête me donne "Data type unknown.")
pour preuve de ce que j'avance, cette requête via flamerobin

Code :

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

1
2
select annee,numero from FACTURES
where DATE_FACTURE between cast ( 2016||'-01-01' as date) and cast ( 2016|| '-01-' || '31' as date)

où DATE_FACTURE est un TIMESTAMP (Base en 2.5, DIALECT 1) fonctionne
or via programme Delphi (D2010/ZEOSDBO) si tel quel le SQL fonctionne, en passant l'année en paramètre, quel que soit le type (string,smallint ou integer) cela plante, pour s'en sortir avec un paramètre de type entier et ce set de composants il faut écrire le SQL suivant

Code :

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

1
2
select annee,numero from FACTURES
where DATE_FACTURE between cast (CAST(:annee AS CHAR(4))||'-01-01' as date) and cast (CAST(:annee AS CHAR(4))|| '-01-' || '31' as date)

Alors que via Delphi il est si simple de garder la formulation originale de la requête et de calculer les 2 dates !

@Foucoudav les raccourcis proposés tel que date '01-01-2010' ne fonctionne pas toujours (d'où l'erreur Token unknown - line 6, column 38) c'est pourquoi je reste à la bonne vieille méthode du CAST (https://firebirdsql.org/refdocs/langrefupd20-cast.html)

Autre base, autre Dialect (3) et autre table
Soit une colonne DEBUT en timestamp
tout en CAST, année en integer : WHERE CAST(DEBUT AS DATE) = CAST( 2015||'-11-30' AS DATE) >> fonctionne
raccourci date sur partie concaténée : WHERE CAST(DEBUT AS DATE) = date '2015-11-30' >> fonctionne
année en integer : WHERE CAST(DEBUT AS DATE) = date 2015||'-11-30' >> ne fonctionne pas (erreur tocken unknown 2015)
raccourci date sur colonne : WHERE date DEBUT= date '2015-11-30' >> ne fonctionne pas (erreur tocken unknown DEBUT)



N.B. je ne vois pas trop à quoi sert la jointure sur CMDCLI

[SQLpro]

Autre formulation moins ambiguë :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT SUM(BL.LBQTE * CC.LCPRIX) AS TOTAL 
FROM   FACTURE AS F
       JOIN L_FACTURE AS LF
            ON F.FAIDFACT = LF.LFIDFACT
       JOIN L_BLIV AS BL
            ON LF.LFIDLBL = BL.LBIDLBL
       JOIN L_CMDCLI AS CC
            ON BL.LBLIDLCDE = CC.LCIDLCDE
       JOIN CMDCLI AS C
            ON CC.LCIDCDE = C.COIDCDE
WHERE  YEAR(F.FADATFACT) = :an
  AND  (MONTH(F.FADATFACT) > 1 OR MONTH(F.FADATFACT) = 1 AND DAY(F.FADATFACT) >= 1)
  AND  (MONTH(F.FADATFACT) < 3 OR MONTH(F.FADATFACT) = 3 AND DAY(F.FADATFACT) <= 31)

En utilisant EXTRACT si FB ne connais pas YEAR et MONTH...

A +

[SergioMaster]

Bonjour,

Pour répondre à SQLPro, Firebird n'a pas YEAR,MONTH,DAY d'implémenté, du moins dans les versions < 3, je n'ai pas vérifié pour la version 3, il faut donc passer par EXTRACT du coup si la formulation est moins "ambiguë" elle est toutefois largement plus "lourde" (je pense que c'est ce qui a valu un de la part d'un lecteur)

Looping ne nous a pas indiqué de quel version il s'agissait, c'est dommage car avec Firebird 3 on aurait pu parler de fonctions PSQL (voir par exemple ceci) mais on pourrait aussi parler (et ce pour toutes versions) des UDF (par exemple celles de FreeAdHocUDF) et ainsi faire quelque chose de plus général.

Mais, après tout qu'en est-il : obtenir une somme sur un ensemble de factures et ce sur un ensemble de périodes (mois) et cela on peut le faire sans se préoccuper du format à fournir mais uniquement des calculs de dates ,en exemple : à partir de la date du jour 3 mois plein

Code :

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

1
2
3
4
SELECT CURRENT_DATE,
DATEADD((EXTRACT(DAY FROM CURRENT_DATE)*-1+1) DAY TO CURRENT_DATE), -- obtention début du mois equivalent de F_FIRSTDAYMONTH(CURRENT_DATE) de FreeAdHocUDF
DATEADD(-1 DAY TO DATEADD(3 MONTH TO DATEADD((EXTRACT(DAY FROM CURRENT_DATE)*-1+1) DAY TO CURRENT_DATE))) -- Obtention début du mois+3 moins 1 jour soit la fin du mois équivalent de DATEADD(2 MONTH TO F_LASTDAYMONTH(CURRENT_DATE)) avec FreeAdHocUDF
FROM RDB$DATABASE

donc, @looping et si on passait une date et un nombre de mois comme paramètre ?
la clause where deviendrait alors

Code :

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

1
2
where DATE_FACTURE between DATEADD((EXTRACT(DAY FROM :d)*-1+1) DAY TO :d)
          AND DATEADD(-1 DAY TO DATEADD(:NBM MONTH TO DATEADD((EXTRACT(DAY FROM :d)*-1+1) DAY TO :d)))

[SQLpro]

donc, @looping et si on passait une date et un nombre de mois comme paramètre ?
la clause where deviendrait alors

Code :

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

1
2
where DATE_FACTURE between DATEADD((EXTRACT(DAY FROM :d)*-1+1) DAY TO :d)
          AND DATEADD(-1 DAY TO DATEADD(:NBM MONTH TO DATEADD((EXTRACT(DAY FROM :d)*-1+1) DAY TO :d)))

ATTENTION, car là cela peut donner des résultats totalement faux ! En effet, le nombre de jours des mois fluctuant tout ajout de mois à une date peut avoir des effets pervers et c'est pourquoi la norme SQL interdit explicitement l'ajout ou le retrait de durée intégrant une granularité temporelle de mois !

A voir comment se comporte FB, car je ne l'ai pas sous la main, mais je serais curieux de savoir ce que donne par exemple le calcul suivant :

Code :

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

SELECT DATEADD(1 MONTH TO '2016-01-31')

A +

A +

[Artemus24]

Salut SQLPRO.

A voir comment se comporte FB, car je ne l'ai pas sous la main, mais je serais curieux de savoir ce que donne par exemple le calcul suivant :

Faudrait-il encore que la requête soit correctement écrite :

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
SELECT DATEADD(MONTH, 1, cast('2017-01-31' as date)) FROM RDB$DATABASE;
 
    DATEADD
===========
2017-02-28
 
SELECT DATEADD(DAY,  -1, cast('2017-03-01' as date)) FROM RDB$DATABASE;
 
    DATEADD
===========
2017-02-28
 
exit;
 
Appuyez sur une touche pour continuer...

Oui, cela fonctionne parfaitement !

c'est pourquoi la norme SQL interdit explicitement l'ajout ou le retrait de durée intégrant une granularité temporelle de mois !

Là, c'est du grand n'importe quoi !
Et pourquoi ne pas remettre en cause aussi, FireBird ?

@+

[SergioMaster]

Bonjour,

@Artemus24 la syntaxe proposée par SQL pro est correcte ou presque puisqu'il ne manquait finalement que d'un Casting soit explicite CAST, soit raccourci DATE et bien sûr d'un FROM RDB$DATABASE (spécifique à Firebird comme Interbase)

Code :

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

1
2
3
SELECT DATEADD(1 MONTH DATE '31.01.2017') FROM RDB$DATABASE; 
SELECT DATEADD(MONTH,1, DATE '31.01.2017') FROM RDB$DATABASE;
SELECT DATEADD(MONTH,1, CAST('31.01.2017' AS DATE)) FROM RDB$DATABASE;

feront toutes 3 la même chose et le résultat en sera 28.02.2017. Par contre, il est vrai qu'il faut se méfier d'un calcul pareil car
SELECT DATEADD(1 MONTH DATE '28.02.2017') FROM RDB$DATABASE ne donnera pas la fin du mois de mars mais 28.03.2017 et là, effectivement, cela poserait problème !

Vous noterez cependant que, de toute façon, le code que je propose travaille toujours sur le premier du mois

Code :

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

DATEADD((EXTRACT(DAY FROM :d)*-1+1) DAY TO :d)

Que la norme SQL interdise explicitement l'ajout ou non de mois ne change rien au fait que cela fonctionnera avec Firebird. Je pense que toutes les SGBD ont quelques écarts (ou idiosyncrasies ?) avec la norme et je ne jetterai pas la pierre à Firebird pour ça. L'important est surtout de savoir comment est traité cette opération.

[Artemus24]

Salut SergioMaster.

la syntaxe proposée par SQL pro est correcte ou presque ...

C'est le presque qui ne me convient pas. En informatique, le presque est sujet à beaucoup d'erreurs.

En ce qui concerne l'un de tes exemples, c'était presque ça. Il manque juste le "TO" avant le mot clef "DATE".

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT DATEADD(1 MONTH    DATE '31.01.2017') FROM RDB$DATABASE;
Statement failed, SQLSTATE = 42000
Dynamic SQL Error
-SQL error code = -104
-Token unknown - line 1, column 27
-DATE
At line 7 in file Test.sql
 
SELECT DATEADD(1 MONTH TO DATE '31.01.2017') FROM RDB$DATABASE;
 
    DATEADD
===========
2017-02-28

Par contre, il est vrai qu'il faut se méfier d'un calcul pareil car SELECT DATEADD(1 MONTH DATE '28.02.2017') FROM RDB$DATABASE ne donnera pas la fin du mois de mars mais 28.03.2017 et là, effectivement, cela poserait problème !

Erreur d'interprétation !

Ajouter 1 mois à une date, en principe, ne doit pas changer le jour, sauf si le jour en question n'existe pas, comme par exemple un 31, 30 ou 29 sur le mois de février qui n'est pas d'une année bisextile.

Pour trouver la date correspondant à la fin d'un mois, il suffit de faire ceci en FireBird :

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
SELECT                                        CAST(EXTRACT(YEAR FROM CURRENT_DATE)||'-'||EXTRACT(MONTH FROM CURRENT_DATE)||'-01' AS DATE)   FROM RDB$DATABASE;
 
       CAST
===========
2017-02-01
 
SELECT                    DATEADD (1 MONTH TO CAST(EXTRACT(YEAR FROM CURRENT_DATE)||'-'||EXTRACT(MONTH FROM CURRENT_DATE)||'-01' AS DATE))  FROM RDB$DATABASE;
 
    DATEADD
===========
2017-03-01
 
SELECT DATEADD (-1 DAY TO DATEADD (1 MONTH TO CAST(EXTRACT(YEAR FROM CURRENT_DATE)||'-'||EXTRACT(MONTH FROM CURRENT_DATE)||'-01' AS DATE))) FROM RDB$DATABASE;
 
    DATEADD
===========
2017-02-28

J'ai décomposé en trois étapes afin de bien mettre en évidence le calcul.

L'astuce consiste à prendre la date du premier jour du mois suivant et de faire -1 sur cette date, pour avoir le dernier jour du mois courant.

Que la norme SQL interdise explicitement l'ajout ou non de mois ne change rien au fait que cela fonctionnera avec Firebird.

La norme n'a jamais été une vérité absolue. C'est juste une préconisation et rien de plus. Maintenant, chaque SGBD peut la suivre ou pas.
En fait, le problème n'est pas là, mais de mettre à la disposition de l'utilisateur les outils permettant de résoudre son problème.
Je préfère de loin le coté pragmatique à la résolution d'un problème, plutôt que le stricto sensus de la norme.
Ce qui compte, c'est que ça fonctionne et que ça réponde à l'attente de l'utilisateur, et ce, sans faire une usine à gaz !

Je pense que toutes les SGBD ont quelques écarts (ou idiosyncrasies ?) avec la norme et je ne jetterai pas la pierre à Firebird pour ça.

Chaque SGBD fait ce qu'il veut. En aucune façon, je ne viendrais critiquer qui que ce soit sur les choix qui ont été faits.
Par contre, à l'inverse, je n'adhère pas nécessairement aux choix qui ont été fait, mais je ferai avec.

L'important est surtout de savoir comment est traité cette opération.

Entièrement d'accord.
A mon avis, c'est la différence qui existe entre le théoricien qui ne jure que par la norme et le pragmatique qui va se contenter de la solution qui fonctionne.
Le pragmatique connaitra par coeur son SGBD alors que le théoricien passera son temps à le critiquer !

P.S.: je t'ai mis des +1 pour contrer la mauvaise notation de SQLPRO !

@+

[SergioMaster]

Bonjour,

En ce qui concerne l'un de tes exemples, c'était presque ça. Il manque juste le "TO" avant le mot clef "DATE".

méa culpa, je voulais en fait surtout indiquer que pour dateadd il y avait deux syntaxe possible, à écrire trop vite, on fait des erreurs

L'astuce consiste à prendre la date du premier jour du mois suivant et de faire -1 sur cette date, pour avoir le dernier jour du mois courant.

c'est bien ce que mon code fait

P.S.: je t'ai mis des +1 pour contrer la mauvaise notation de SQLPRO !

Bah, je ne sais pas si c'était nécessaire, en fait j'aurai certainement préféré un retour de @looping (au pire un simple indication de son problème était )

[Artemus24]

Salut SergioMaster.

à écrire trop vite, on fait des erreurs

Il n'y a pas de mal.
Vous interventions sont toujours d'une excellente qualité, à l'inverse de SQLPRO qui ne prend même pas la peine de les tester !

c'est bien ce que mon code fait

Je ne remets pas en cause votre code.
Je voulais simplement illustrer par une astuce qu'il était tout à fait possible, en FireBird, de calculer le dernier jour d'un mois quelconque, puisque SQLPRO émettait des doutes sur sa faisabilité.

@+