[10g][PL/SQL] To_date et date format

**gojira** · 19/10/2006, 16h14

Bonjour,

Je reviens vers vous la queue entre les jambes vous demander umblement votre aide.
En effet j'ai une function PL/SQL qui a en entrée un parametre date et un parametre format de date.

Le but est l'execution d'une requete du genre

Code :

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

select count(*) from table where table.column_date < to_date( une_chaine, le_format_de_date)

lorsque je passe en parametre le format 'YYIW' ou 'YYWW' j'ai l'erreur

Envoyé par le vilain compilateur oracle tout pas sympa

ORA-01820: format code cannot appear in date input format

En effet, il m'arrive d'avoir des dates au format 'YYYY' (une année) ou au format 'YYMM' (année-mois) mais aussi avec la semaine (YYWW) ou (YYIW):
exemple : 0652 pour la dernière semaine de décembre de l'année 2006

et j'avoue ne pas savoir comment faire pour passer outre.

J'avais dans l'idée de passer par une comparaison de number:

convertir la colonne date de la table en number au format de la date en input (VARCHAR2)
convertir la date en input en number et faire de la comparaison de number!

Mais y a-t-il quelque chose de plus simple?

Merci pour toute l'aide que vous pourrez m'apporter.
Cordialement

**orafrance** · 19/10/2006, 16h17

si tu as un mélange de tout ça dans la table t'aura forcément des problèmes de format... on mélange pas les torchons et les serviettes... là tu es confronté à une vraie faute de conception

**orafrance** · 19/10/2006, 16h17

Envoyé par gojira

J'avais dans l'idée de passer par une comparaison de number:

et comment tu sais que 2010 c'est l'année ou octobre 2020 ?

**gojira** · 19/10/2006, 16h25

en fait la valeur de la date et le format est donné en input par le programme qui utilise ces procédure SQL
Donc je sais toujours que la date en parametre va de paire avec le format
si j'ai (2010, 'YYMM') je sais que c'est octobre 2020
si j'ai (2010, 'YYYY') je sais que c'est 2010

pour l'histoire de problème de conception...

ne m'en parlez pas

merci à toi pour tes réponses

**orafrance** · 19/10/2006, 16h28

ça j'ai bien compris mais si dans la table tu n'as pas le format pour la ligne t'es mort

**gojira** · 19/10/2006, 16h55

A priori la colonne de la table est au format date
DOnc je suppose que le format de la colonne dépend du format que je veux.
Je peux obtenir 'DD-mm-YYYY HH:mm:ss' ou 'DDMMYY', etc...

Non? Le format stocké dans l'objet date est particulier?
En fait je ne saisis pas trop ta question.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
FUNCTION get_history(start_date IN VARCHAR2, 
			end_date IN VARCHAR2,
			time_format IN VARCHAR2)
RETURN VARCHAR2
IS
	requete VARCHAR2(4000);
	target_col VARCHAR2(100);
BEGIN
	target_col := 'TO_NUMBER(TO_CHAR(maTable.column_date, ''' ||
				time_format || '''))';
	requete:=  target_col || ' BETWEEN TO_NUMBER (''' || start_date || ''')' ||
			' AND TO_NUMBER (''' || end_date || ''')';
	RETURN (requete);
END get_history;

A priori en faisant ça , ça marche bien.
Mon but est de cracher le bout de requête qui permettra de faire la discimination.

Le code ci-dessus marche bien, mais ça veut dire appliquer deux transformation à une colonne... c'est lourd.

Je cherche donc quelque chose de moins couteux.

Merci beaucoup

**remi4444** · 19/10/2006, 18h24

Je comprends pas, si tu passe le format dans la fonction et qu'il y a une erreur au final c'est bien que c'est à l'appel de la fonction qu'il y a un problème non ?
en tout cas il faut absoluement que tu trace, tu peux capter toi meme les erreurs pour faire un RAISE_APPLICATION_ERROR et y indiquer dans le message la donnée et le format qui ont fait planté.

Exemple:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
FUNCTION get_history(start_date IN VARCHAR2, 
			end_date IN VARCHAR2,
			time_format IN VARCHAR2)
RETURN VARCHAR2
Err varchar2(200);
 
BEGIN
 ....blabla blablabla....
EXCEPTION WHEN OTHERS THEN
  Err := substr(Err,1,200);
  RAISE_APPLICATION_ERROR (-20001,'p1='||start_date||' p2='||end_date||' p3='||time_format||' Err:'||Err);
END;
/

**rbaraer** · 20/10/2006, 11h35

Ce sont les formats en 'IW' ou 'WW' qui posent problème : ils ne sont pas compatibles avec TO_DATE. Cf Datetime Format Models, tableau 2.16, colonne "Specify in TO_* datetime functions?".

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
rbaraer@Ora10g>  select to_char(sysdate,'WW') from dual;
 
TO
--
42
 
rbaraer@Ora10g>  select to_date(to_char(sysdate,'WW'),'WW') from dual;
 select to_date(to_char(sysdate,'WW'),'WW') from dual
                                      *
ERROR at line 1:
ORA-01820: format code cannot appear in date input format
 
 
rbaraer@Ora10g>

Mais comme vous le voyez ça marche avec TO_CHAR.

Donc, une solution est de faire :

Code :

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

SELECT count(*) FROM TABLE WHERE TO_CHAR(TABLE.column_date, le_format_de_date) < une_chaine

Cependant il faut que le format se trie comme une date, comme 'YYYYMMDD' ou 'YYYYWW' par exemple et l'inconvénient est qu'un index sur la colonne date ne serait pas utilisé, il faudrait créer un index sur la fonction TO_CHAR(column_date) (et ce pour chaque format

).

Une autre solution est de tester le format et s'il est compatible avec TO_DATE l'utiliser directement, sinon faire une conversion en date "à la main". Pour cela vous pouvez soit implémenter les règles des formats WW et IW (ce qui n'est sûrement pas évident), soit créer une table ou une vue avec toutes les dates concernées et 3 colonnes : la date au format DATE et dans les deux autres TO_CHAR(date, 'WW') et TO_CHAR(date, 'IW') (puisque le TO_CHAR marche

), puis rechercher la date à partir du format dans cette table. Une table serait plus performante qu'une vue car l'optimiseur aurait plus d'informations.

Sinon, ce serait mieux de ne jamais avoir de format non compatible avec TO_DATE mais peut-être ne maîtrisez-vous pas cette partie

.

Cordialement,

rbaraer

**pedro179** · 02/08/2007, 12h35

Bonjour,
Je remercie les rédacteurs pour ce forum unique au monde.

P.S : si seulement Monsieur Orafrance pourrait dans le futur donner des réponses ou des indications plus précises au lieu de ...

**McM** · 02/08/2007, 12h47

Envoyé par pedro179

P.S : si seulement Monsieur Orafrance pourrait dans le futur donner des réponses ou des indications plus précises au lieu de ...

Tu peux préciser ta pensée ?

**Garuda** · 02/08/2007, 12h50

Envoyé par pedro179

P.S : si seulement Monsieur Orafrance pourrait dans le futur donner des réponses ou des indications plus précises au lieu de ...

Et si Monsieur pedro179 pouvait écrire correctement le français !

**magicbisous-nours** · 13/12/2007, 15h36

Bonjour je viens vers vous car j'ai moi aussi un problème avec la fonction to_date : j'ai une procédure qui récupère une date dans un fichier de format DD-MON-YY et quand je fais :

Code :

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

1
2
select to_date('13-Dec-07', 'DD-MON-YY')
from dual;

PL/SQL me répond :

Code :

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

invalid month

pourtant dans les tutos sur ce site je l'ai vu ce format....

pourrait-on m'aider s'il vous plaît?
merci d'avance.

**Garuda** · 13/12/2007, 15h43

Je pense qu'il s'agit d'un problème de langue pour la date.
Vérifie la variable
'NLS_DATE_LANGUAGE' coté serveur et coté client

**magicbisous-nours** · 13/12/2007, 15h58

c'est ce qu'on m'a répondu après au boulot mais comment je peux faire pour modifier ceci??
mon tuteur se rappelle avoir fait une fois un alter session nls language mais il ne se souvient plus comment on fait

...
merci d'avance

**Garuda** · 13/12/2007, 16h02

Eh ben,
tu fais une recherche sur le site avec 'ALTER SESSION' ou 'NLS_DATE_LANGUAGE'
C'est ub problème récurrent !!!!

**magicbisous-nours** · 13/12/2007, 16h20

c'est bon merci....par contre je n'ai pas trouvé pour mon nouveau problème :
j'ai une heure aussi dans mon fichier que je veux stocker dans la même table mais un champ à part de la date...et quand je fais :

Code :

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

1
2
to_date('13::06:11','HH24:MI:SS')
FROM dual;

ben j'ai une date (le premier décembre de cette année) avant l'heure saisie :s...
comment puis-je remédier à ce problème svp??

**Garuda** · 13/12/2007, 16h29

1) Inutile de faire un nouveau post !

2) Le format HEURE n'existe pas en tant que tel dans ORACLE
Tu peux stocker une chaine de caractéré du genre

Code :

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

1
2
3
 
SELECT TO_CHAR (TO_DATE ('13:06:11', 'HH24:MI:SS'), 'HH24:MI:SS')
  FROM DUAL;

**magicbisous-nours** · 13/12/2007, 16h31

ok merci...
dans ce cas je vasi coller la date et l'heure dans le même champs puisque je n'ai pas le choix...
encore merci de tes réponses rapides

**mnitu** · 13/12/2007, 17h28

C’est dingue mais je jamais vue autant des confusions.

Le type date c’est une date ! Le type date n’a pas de format ! Le formatage intervient seulement en entrée et en sortie. Il y a des détails ici. Sinon la procédure qui prend le format en entrée c’est n’importe quoi (désolé). Je pense que orafrance l’a dit aussi très clairement.