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

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:

---

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

Citation:

---

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

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 :?

Citation:

---

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 ?

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... :roll: ne m'en parlez pas :furax:

merci à toi pour tes réponses

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

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:

---

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 :ave:

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:

---

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; /

---

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:

---

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:

---

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 :roll:.

Cordialement,

rbaraer

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 ... :?

Citation:

---

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 ... :?

---

:question: :!: :question:
Tu peux préciser ta pensée ?

Citation:

---

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 !

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:

---

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

---

PL/SQL me répond :

Code:

---

invalid month

---

pourtant dans les tutos sur ce site je l'ai vu ce format....8O
pourrait-on m'aider s'il vous plaît?
merci d'avance.

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

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

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

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:

---

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??

1) Inutile de faire un nouveau post !:aie:
2) Le format HEURE n'existe pas en tant que tel dans ORACLE
Tu peux stocker une chaine de caractéré du genre

Code:

---

1 2 3

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

---

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 ;)

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.

Citation:

---

Envoyé par mnitu

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

---

Ok avec toi.
Mais ce n'est peut-être pas la peine d'en rajouter (de la confusion !)8O
Il s'agit de la reprise d'un vieux post !!!!!!:mrgreen:

j'ai une chaîne de la forme : 13-Dec-07 13:06:25.
quand je fais un to_date('13-Dec-07 13:06:25', 'DD-MON-YY', 'NLS_DATE_LANGUAGE = ENGLISH') dans un select dans une fenêtre sql ça fonctionne....mais si je stocke ma chaine dans uen variable et que je fais mon to_date dans une procédure ben la variable ne retient que la date et pas l'heure.... Pourtant ma variable est bien de type date.....

parce qu'il faut appliquer une règle d'affichage genre :

Code:

---

TO_CHAR(la_variable,'DD/MM/YYYY HH24:MI:SS')

---

C'est parce que ton to_date ne retient pas l'heure !

Code:

---

1 2 3

to_date('13-Dec-07 13:06:25', 'DD-MON-YY HH24:MI:SS', 'NLS_DATE_LANGUAGE = ENGLISH')

---

c'est bon merci avec mes collègues de travail on a trouvé : on a mis au début de la procédure ces lignes :

Code:

---

1 2	EXECUTE IMMEDIATE 'alter session set nls_date_format = ''DD/MM/YYYY HH24:MI:SS'''; EXECUTE IMMEDIATE 'alter session set nls_language = ''AMERICAN''';

---

et là ça fonctionne.
en tout cas merci à vous pour vos réponses ;)

je reviens de nouveau vers vous car je cherche depuis quelques temps déjà comment, dans une requête, ne récupérer que le jour.
j'explique les raisons de ma recherche : je fais une application en PHP appuyée sur une base oracle et j'ai besoin de forcer le format d'affichage de la date en PHP car dans ma base la date est au format DD/MM/YYYY HH24:MI:SS mais lorsque je me contente d'afficher le résultat de ma requête, PHP ne m'affiche pas l'heure or j'en ai besoin....pour ça j'ai trouvé qu'il fallait que j'utilise la fonction mktime() de php mais les paramètres sont séparés : jour, mois, etc...
comme ceci : mktime(heure, minutes, secondes, mois, jour, an);
et donc je voudrais récupérer tous ces paramètres de ma requête en sachant que je n'ai la date que dans un champ de ma table.
en espérant avoir été clair
merci d'avance

c'est bon j'ai trouvé :
to_char(date, 'dd') pour le jour
merci quand même!!