Mettre en forme une date [Résolu]

[francois78]

Bonjour

Je m’initie (avec plus ou moins de difficulté mais avec plaisir) depuis quelques temps à requêter des bases Oracles avec SQL_Developper.
Voilà qu’aujourd’hui, je suis confronté à un problème, sûrement simple, mais dont je n’arrive pas à trouver la solution.

C’est un problème de mise en forme de donnée.

Je récupère des valeurs représentant une date mais au format YYYYMMDD (ex : 20111221).
Ces valeurs sont insérées dans une table dont le champ est de type Varchar2 (8 byte)
L’insertion ne pose aucun problème. Je retrouve bien mes valeurs dans ma table au format YYYYMMDD.

Mais je souhaite dans un 1er temps affiché le contenu de ces dates dans un format plus lisible de type DD/MM/YYYY (ex : 21/12/2011)

J’ai essayé plusieurs synthaxe avec to_char ou to_date mais rien n’y fait.
Par exemple :

Code :

1
2
3
4
5
6
7
8
Select to_char(‘Ma_Date’,’YYYYMMDD’) from Ma_Table
Select to_char(Ma_Date,’YYYYMMDD’) from Ma_Table
Select to_date(‘Ma_Date’,’YYYYMMDD’) from Ma_Table
Select to_date(Ma_Date,’YYYYMMDD’) from Ma_Table

Des variantes avec des to_char et to_date imbriquées l’un dans l’autre

Mais rien n’y fait. Je n’ai pas de message d’erreur. Seulement, un affichage vide du résultat.

Je ne pense pas que la donnée insérée soit en cause car j'ai essayé sur une autre table avec des valeurs que j'ai insérée manuellement et là non plus, rien de rien...

J’avoue que je sèche un peu et je ne trouve pas mon erreur dans les tutos du site.

Si quelqu’un peut me conseiller, je suis preneur.

Bonne journée

[lola06]

Bonjour,

Tout d'abord tu as une erreur dans ta requête :

Code :

1
2
SELECT to_char(MaDate,'DD/MM/YYYY')
FROM MaTable

Il faut mettre le format que tu veux avoir et non pas le format que tu as déjà.

Si tu as un affichage vide c'est qu'il n'y a aucune ligne retournée. Peux-tu nous montrer ta requête complète ?

[McM]

table dont le champ est de type Varchar2 (8 byte)

Ton but est de transformer une chaine en date.. donc pas de TO_CHAR !

TO_DATE pour transformer une Chaine en Date
TO_CHAR pour transformer une Date en Chaine

poste le résultat de la requête suivante

Code :

SELECT madate, to_date(madate, 'YYYYMMDD') FROM matable WHERE rownum < 10

[spdev666]

Bonjour,

Comme les données d'origine ne sont pas stockées comme des dates, il faut peut être faire 2 conversions :

Code :

1
2
3
4
5
6
7
 
WITH t AS (
SELECT '20110101' a FROM dual union ALL
SELECT '20111201' a FROM dual union ALL
SELECT '20111231' a FROM dual 
)
SELECT TO_DATE(TO_DATE(A, 'yyyymmdd'), 'dd/mm/yyyy') FROM T

[McM]

Oh non !! Jamais de to_date sur une date

[Waldar]

Envoyé par francois78

Je récupère des valeurs représentant une date mais au format YYYYMMDD (ex : 20111221).
Ces valeurs sont insérées dans une table dont le champ est de type Varchar2 (8 byte)

C'est une des pires erreurs (et des plus courantes aussi) qu'il soit possible de faire en terme de base de données : ne pas utiliser le bon type.
Rien n'empêchera d'insérer la donnée 20111313 dans votre varchar.

Modifiez votre table, transformez votre colonne au format DATE.

Comme suggéré, vous devez faire une double conversion :

Code :

1
2
3
4
5
6
7
8
WITH t AS
(
SELECT '20110101' a FROM dual union ALL
SELECT '20111201'   FROM dual union ALL
SELECT '20111231'   FROM dual 
)
SELECT TO_CHAR(TO_DATE(A, 'yyyymmdd'), 'dd/mm/yyyy') AS dt
  FROM T;

[lola06]

Autant pour moi, j'ai lu trop vite et n'avais pas bien vu le format de la colonne.

Par curiosité : est-il possible de faire directement :

Code :

1
2
 SELECT to_date(MaDate,'DD/MM/YYYY')
FROM MaTable

Est-ce qu'il reconnait de lui-même les DD, MM et YYYY ?

[McM]

Non, le format de To_date correspond précisément à ce qu'il y a dans la colonne.. donc non oracle ne va pas deviner..

[laurentschneider]

parfois il devine quand même un tout petit peu ...

Code :

1
2
3
4
5
6
 
SQL> SELECT to_date('1-nov-1811','DD/MM/RR') FROM dual;
 
TO_DATE(1-NOV-1811
-------------------
1811-11-01_00:00:00

pour ces bizarreries, se référer à la doc :
http://docs.oracle.com/cd/E11882_01/...04.htm#g195479

[francois78]

Ouahou!!!!!!! Je vois que ma demande déchaîne les passions !!

Après avoir lu quelques sujets sur différents forum, la maîtrise des données n'est pas acquise pour tout le monde, en particulier celle des conversion de date. Je parle pour moi bien sûr

Envoyé par McM

Ton but est de transformer une chaine en date.. donc pas de TO_CHAR !

TO_DATE pour transformer une Chaine en Date
TO_CHAR pour transformer une Date en Chaine

poste le résultat de la requête suivante

Code :

SELECT madate, to_date(madate, 'YYYYMMDD') FROM matable WHERE rownum < 10

En attendant vos réactions, j'essaie de suite et vous tiens au courant
Résultat
DATE_CREATION TO_DATE
---------------------- -------------------------
20110913 13/09/11
20101203 03/12/10
20070419 19/04/07
20110326 26/03/11
20101202 02/12/10
20101203 03/12/10
20110913 13/09/11
20110927 27/09/11
20110924 24/09/11

9 lignes sélectionnées


Mais en lisant et en relisant vos réponses, j'ai réfléchi avec ma tête et sur cette réponse ::

Envoyé par Waldar

C'est une des pires erreurs (et des plus courantes aussi) qu'il soit possible de faire en terme de base de données : ne pas utiliser le bon type.
Rien n'empêchera d'insérer la donnée 20111313 dans votre varchar.

Modifiez votre table, transformez votre colonne au format DATE.

En regardant de plus près ma table, j'ai une donnée dans mon champ VARCHAR2 = 0

Si je l'écarte alors ça marche beaucoup mieux

Pour résumé :

1. Je transforme mon VARCHAR2 en DATE
2. Au moment de l'insertion, je converti la donnée comme ceci :

Code :

1
2
INSERT INTO TO_CHAR(TO_DATE(A, 'yyyymmdd'), 'dd/mm/yyyy') AS dt
  FROM T;

En attendant vos réponse ou vos réactions, j'essaie et je vous tiens au courant.

A tout à l'heure

[francois78]

Ca fonctionne mais pas du 1er coup.

Pourquoi ? Comme l'a très bien dit

Envoyé par Waldar

C'est une des pires erreurs (et des plus courantes aussi) qu'il soit possible de faire en terme de base de données : ne pas utiliser le bon type.
Rien n'empêchera d'insérer la donnée 20111313 dans votre varchar.

il faut donc maîtriser les données insérées dans une table.

Seulement, j'ai un cas particulier qui coince.

Je précise que je suis utilisateur habilité uniquement à requêter dans les bases ORACLE mais je ne recherche pas des problèmes de cohérence de donnée mais plutôt un dysfonctionnement de notre système. Toutefois, pour une obtenir un résultat exploitable issue de plusieurs tables, je créé mes propres tables et d'où ma question initiale sur le formatage de date.

Mon cas particulier est le suivant : je suis amené à requêter une table avec des champs contenant une date au format YYMMDD mais son type de data est VARCHAR2.
Sous certaines conditions, il est parfois logique qu'aucune date ne soit indiquée dans ce champ. Cela se traduit par une valeur = 00000000

Évidement, avec ce que nous avons vu ci-dessus, nous arrivons à la conclusion que nous ne pouvons pas insérer une telle valeur dans un champ date.

Et là, je bloque totalement

Dois je modifier ma requête ou dois je modifier les caractéristiques du champ DATE de ma table pour prendre en compte ce cas particulier ?

Je suis preneur de solutions diverses et variées.

Merci d'avance.
Bonne soirée

[pacmann]

Salut !

Pourquoi pas transformer ce pas de date en NULL ?

Code :

1
2
3
4
5
 
SELECT to_date(nullif(tadate,'00000000'), 'yymmdd')  
FROM (
    SELECT '20120101' tadate FROM dual union ALL
    SELECT '00000000' tadate FROM dual) t

[francois78]

Pacmann

Tu m'as devancé mais je prends pour ma culture ton exemple que j'essaierai.

J'ai trouvé une autre solution avec un case :

Code :

1
2
3
4
case Ma_Date
when '00000000' then '01/01/01'
else TO_CHAR(TO_DATE(Ma_Date, 'yyyymmdd'), 'dd/mm/yyyy')
end

Si d'autres idées, exprimez vous.
La nuit porte conseil et je clôturais la demande demain matin

Bonne nuit

[lola06]

Le problème de ton case c'est que dans ton exemple tu remplace '00000000' par une date qui peut aussi apparaitre ailleurs dans ta base de données. Il vaut mieux remplacer par null.
Si tu tiens à utiliser le case alors la syntaxe c'est :

Code :

1
2
3
4
case Ma_Date
when '00000000' then NULL
else TO_CHAR(TO_DATE(Ma_Date, 'yyyymmdd'), 'dd/mm/yyyy')
end

[francois78]

Bonjour à tous

Pour répondre à Lola06, la date du 01/01/01 ne peut apparaître ailleurs dans la mesure où mon SI n'existait pas à cette époque.

Mais rien n'est impossible. De plus, ta solution reflète plus la réalité car l'absence de date est le reflet des événements de notre SI.

J'ai testé et cela fonctionne.

Merci à tous et bonne journée

Et attention au chocolat pour les prochains jours....

[pacmann]

Tout à fait, cela reflète mieux la réalité, et cela évite un certain nombre de désagréments de la valeur bidon.

Par exemple, les statistiques basiques te calculent le nombre de NULL sur une colonne, alors que pour prendre en compte le biais induit par une valeur bidon, il faut des histogrammes...

[laurentschneider]

ce qui est chouette c'est d'insérer des valeurs bidons dans la base, comme ça nous les DBA on a toujours du boulot

sqlplus 11.2.0.3

Code :

1
2
3
4
5
6
7
8
9
10
SQL> CREATE TABLE t(d date);
TABLE created.
SQL> INSERT INTO t VALUES (round(date '9999-12-31','MM'));
1 row created.
SQL> commit;
Commit complete.
SQL> SELECT d FROM t;
D
---------
01-JAN-00

Toad 11.1.0.17

Code :

1
2
3
4
5
6
7
8
9
10
SQL> CREATE TABLE t(d date)
TABLE created.
SQL> INSERT INTO t VALUES (round(date '9999-12-31','MM'))
1 row created.
SQL> commit
Commit complete.
SQL> SELECT d FROM t
D        
---------
01-JAN-01

La différence est subtile

[skuatamad]

Envoyé par pacmann

Par exemple, les statistiques basiques te calculent le nombre de NULL sur une colonne, alors que pour prendre en compte le biais induit par une valeur bidon, il faut des histogrammes...

D'autant que sans histogramme, la valeur bidon va perturber le calcul du facteur de filtrage pour les opérations de type range (>,< )

Envoyé par laurentschneider

La différence est subtile

C'est horrible ça...