Discussion :

[Totorote]

Bonjour,

J'ai une colonne avec différents formats date:
CREATE_DT
2019-05-08
04/03/2020

Je souhaite que tous les formats date de cette colonne ait le format 'DD/MM/YYYY'. J'ai fait ceci :

Code :

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

to_char(CREATE_DT,'DD/MM/YYYY')

Mais ça ne fonctionne pas , j'obtiens l'erreur oracle suivante :
ORA-01722: invalid number


Merci d'avance pour votre aide

[Ikebukuro]

Et ton TO_CHAR, tu l'utilises comment? Dans un SELECT? Si oui, merci de nous donner le SELECT précis.
Autre chose, ta colonne CREATE_DT est de quel type? DATE, VARCHAR2...?

[vanagreg]

Bonjour,

Si ta colonne qui doit contenir des dates n'est pas de type DATE, alors celui qui a défini la colonne est un idiot.
Si ta colonne est de type DATE, alors il n'y a pas de notion de format. Une date est stockée dans un format interne dans la base. Voir cette note pour l'explication (nécessite un accès au support Oracle):

How does Oracle store the DATE datatype internally? (Doc ID 69028.1)
https://support.oracle.com/epmos/fac...ay?&id=69028.1

Si la colonne n'est pas de type DATE, comment sais-tu à quoi correspond les différentes composantes si le format n'est pas le même?

[Totorote]

Merci pour votre retour

la colonne est de type varchar

J'utilise cette colonne dans un select tout simple :

Code :

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

1
2
SELECT TO_CHAR(CREATE_DT, 'DD/MM/YYYY') as CREATE_DT
           FROM PO_SPAIN

[escartefigue]

Je rejoins Vanagreg dans sa remarque.
Pouvez-vous publier le DDL exact de création de la table

Si le but est de restituer un format particulier d'une colonne de type date, une colonne supplémentaire n'est pas nécessaire, à la rigueur une vue si le besoin est récurrent.
Si au contraire la colonne d'origine n'est pas un type date, il faut prévoir un contrôle, car tout est possible (31 février par exemple, ou même "toto", tantôt AA-MM-JJ, JJ-MM-AA, AA-JJ-MM...)

[Totorote]

Je travaille sur Dataiku, de ce fait je n'ai pas accès au DDL pour le moment.

Je peux uniquement voir les données de la table que je dois transformer.

Après avoir bien chercher, j'ai pu voir que je n'avais pas uniquement des dates dans cette colonne, c'est pourquoi j'avais l'erreur Oracle.

Je souhaite donc checker si c'est une date alors j'effectue la transformation sinon null

Code :

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

Case when CREATE_DT (is date) then DD/MM/YYYY ELSE Null end

Je ne sais pas si c'est possible de faire quelque chose de ce genre en SQL ?

[vanagreg]

Quelle est la version de ta base de données?
Depuis la 12.2 il existe la fonction VALIDATE_CONVERSION qui permet de vérifier qu'une conversion est valide:

Code :

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

case when validate_conversion(CREATE_DT as date, 'DD/MM/YYYY') = 1 then to_date(CREATE_DT, 'DD/MM/YYYY') end

[Totorote]

Merci @Vanagreg pour ta solution, cependant la fonction validate_conversion n'est pas reconnue par la base de données, donc je pense que nous avons une version antérieure à la 12.2

Après avoir fait quelques modifications, il est vrai qu'il y avait certaines données, qui n'étaient pas des dates, qui n'avaient rien à faire dans cette colonne.
Désormais j'ai une colonne avec uniquement des dates mais elles ont des formats différents.

Soit j'ai des dates au format 'DD/MM/YYYY'
Soit des dates au format 'YYYY-MM-DD'.
Je souhaite donc que toutes les dates soient au format 'DD/MM/YYYY'.

Je sais que je dois utiliser un "Case when" cependant je ne sais pas comment dire à la BDD :
Case when CREATE='YYYY-MM-DD' then 'DD/MM/YYYY' ELSE 'DD/MM/YYYY' END.

[vanagreg]

Si tu veux remplacer le format YYYY-MM-DD par DD/MM/YYYY alors tu peux faire:

Code :

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

1
2
3
update tatable
set create_dt = regexp_replace(create_dt, '^([[:digit:]]{4})-([[:digit:]]{2})-([[:digit:]]{2})$', '\3/\2/\1') 
where instr(create_dt, '-') > 0