Falsches Date-Format konvertieren

[ostpower]

Servus, folgende Situation:

Ich hab eine varchar2-Spalte mit Datumswerten in folgendem Format: z.b. 31-DEZ-05

Ich möchte das nun gerne konvertieren in 31.12.2005 da das das korrekte Date-Format ist.

Wie würdet ihr das machen. Ich habe mir überlegt das in Substrings zu zerlegen und umzuwandeln. Weiß aber nicht genau ob es vielleicht eine einfachere Methode gibt und wenn nicht wie ich das genau mache mit den Substrings. Kann mir vielleicht jemand weiterhelfen?

(Ich würde das dann mit SQL bzw. PL/SQL machen auf einer 10er Oracle-Datebank)

[dr.dimitri]

Hi,

du solltest ein Datumsfeld immer auch in einem DATE Feld speichern, das macht vieles einfacher. Falls möglich, wärs also gut, wenn Du das änderst.

Zu deinem eigentlichen Problem:

SELECT to_char(to_date(spalte,'DD-MON-YY),'DD.MM.YYYY') FROM tabelle

Mehr dazu findest Du hier TO_DATE und hier Format Models

Dim

PS: Hat das mit dem INSERT funktioniert?

[ostpower]

Ja das mit dem insert hat geklappt, ich habs dann zwar noch etwas anders gemacht, aber es hat geklappt, dafür schon mal danke, jetzt probier ich das mit dem date-feld.

[ostpower]

Ehm kann ich bei der Datums-Konvertierung auch mehrere Optionen angeben bzgl der Quelldaten? Also weil in der Ausgangsspalte nicht komplette alle Datum im Format vorliegen.

also geht in etwas sowas?

SELECT to_char(to_date(spalte,'DD-MON-YY' or 'YYYY-MM-DD'),'DD.MM.YYYY') FROM tabelle

[dr.dimitri]

Hi,

nein so etwas geht nicht. Hier müssen die Daten vorher bereinigt und dann am besten gleich in einer DATE Spalte abgespeichert werden.

Dim

[ostpower]

Kann ich die Datumstruktur abfragen, also vom prinzip her so?:

select datum from tabelle

where datum like 'DD-MON-YY';

[dr.dimitri]

Du kannst Reguläre Ausdrücke verwenden. Du musst dir eben einen passenden Regulären Ausdruck bauen, dann kannst darauf selektieren.

Dim

[ostpower]

ich verstehs net, ich hab den dein statement genommen:

SELECT to_char(to_date(a,'DD-MON-YY'),'DD.MM.YYYY') "Blubb"

FROM tmp_tbl_eff1;

also a ist die spalte aber sagt dann "not a valid month" ob wohl alle datums-daten im format DD-MON-YY vorliegen

[dr.dimitri]

Dann hast im Client evtl. keine deutschen Ländereinstellungen.

SQL> create table test (datum date);


Tabelle wurde erstellt.


SQL> insert into test values(to_date('01.12.2008','DD.MM.YYYY'));


1 Zeile wurde erstellt.


SQL> alter session set nls_language=american;


[b]Session altered.[/b]


SQL> select to_char(datum,'DD-MON-YYYY') from test;


TO_CHAR(DAT

-----------

01-[b]DEC[/b]-2008


SQL> alter session set nls_language=german;


[b]Session wurde geändert.[/b]


SQL> select to_char(datum,'DD-MON-YYYY') from test;


TO_CHAR(DAT

-----------

01-[b]DEZ[/b]-2008

Beachte die veränderten Ausgaben. Da Du bei dir (was ich gesehen hab) wohl deutsche Monatsbezeichner drinnen hast, musst Du auf jeden Fall auch deutsche Ländereinstellungen verwenden.

Dim

[ostpower]

wo hast du des gesehn, also es geht jetzt, es war auf deutsch eingestellt, aber da die daten alle englisch sind also dec statt dez gehts jetzt nachdem ich auf american umgestellt hab.

[dr.dimitri]

wo hast du des gesehn

In deinem Ursprungspost hast Du geschrieben:

Ich hab eine varchar2-Spalte mit Datumswerten in folgendem Format: z.b. 31-DEZ-05

Dim

[ostpower]

Ok ich hab en ganz komisches problem. Also ich hab die Date-Formatierung schonmal erfolgreich durchgeführt, jetzt wollt ich des allerdings nochmal machen und jetzt bekomm ich auf einma ne fehlermeldung...zum verzweifeln. Also folgende Situation:

Also ich habe mir die Datums-Angaben in eine zwischentabelle kopiert. in einer spalte stehn jetzt alle datum im format DD-MON-YY und in der anderen spalte die korrekten datum im format DD.MM.YYYY

So und jetzt mach ich bzw. will ich mit folgedem update die eigentliche spalte in der ursprungstabelle updaten damit dort dann alle datum im richtigen format vorliegen.

update ursprungstabelle

set datumsspalte = (SELECT to_char(to_date(datumsspalte,'DD-MON-YY'),'DD.MM.YYYY')

FROM ursprungstabelle

where exists (

select a

from zwischentabelle

where ursprungstabelle.datumsspalte=zwischentabelle.a

));

Achso und es kommt dann der fehler

The following error has occurred:

ORA-01843: not a valid month

[dbwizard]

Ok ich hab en ganz komisches problem. Also ich hab die Date-Formatierung schonmal erfolgreich durchgeführt, jetzt wollt ich des allerdings nochmal machen und jetzt bekomm ich auf einma ne fehlermeldung...zum verzweifeln. Also folgende Situation:

Also ich habe mir die Datums-Angaben in eine zwischentabelle kopiert. in einer spalte stehn jetzt alle datum im format DD-MON-YY und in der anderen spalte die korrekten datum im format DD.MM.YYYY

So und jetzt mach ich bzw. will ich mit folgedem update die eigentliche spalte in der ursprungstabelle updaten damit dort dann alle datum im richtigen format vorliegen.

update ursprungstabelle

set datumsspalte = (SELECT to_char(to_date(datumsspalte,'DD-MON-YY'),'DD.MM.YYYY')

FROM ursprungstabelle

where exists (

select a

from zwischentabelle

where ursprungstabelle.datumsspalte=zwischentabelle.a

));

- Ja, und was kommt für eine Fehlermeldung ? To many row's in Subquery ?

- und speichere doch, wenn du eh schon dabei bist, die konvertierten Daten in eine neues Feld vom Typ DATE....das erspart dir viel Ärger in der Zukunft....

[dr.dimitri]

Warum verwendest Du keine DATE Spalte wie ich es bereits vor Wochen vorgeschlagen habe?

Vergiss diese VARCHAR2 Spalte und wandle es in eine DATE Spalte um.

Dim

[ostpower]

Wie meinst du das jetzt? also welche Date-spalte in welcher tabelle jetzt?

[dbwizard]

Wie meinst du das jetzt? also welche Date-spalte in welcher tabelle jetzt?

Er meint (und ich auch), dass du in deiner Ursprungstabelle ein neues Feld vom TYP DATE erstellst und deine Datumswerte ab nun dort speichern sollst. Datumswerte gehören IMMER Attribute vom Typ DATE

Gruss

[ostpower]

Ah STOP die falsche datumstyp-anzeige war durch die sprache, mit american geht das, aber ich bekomme dann folgende fehlermeldung, das hast du (dbwizard) doch auch gemeint, also an was liegt das?

The following error has occurred:

ORA-01427: single-row subquery returns more than one row

[ostpower]

Also nochmal zum verständnis ich führe folgenden code aus:

update ursprungstabelle

set datumsspalte = (SELECT to_char(to_date(datumsspalte,'DD-MON-YY'),'DD.MM.YYYY')

FROM ursprungstabelle

where exists (

select a

from zwischentabelle

where ursprungstabelle.datumsspalte=zwischentabelle.a

));

und bekomme diese fehlermeldung:

The following error has occurred:

ORA-01427: single-row subquery returns more than one row

[dbwizard]

Ah STOP die falsche datumstyp-anzeige war durch die sprache, mit american geht das, aber ich bekomme dann folgende fehlermeldung, das hast du (dbwizard) doch auch gemeint, also an was liegt das?

The following error has occurred:

ORA-01427: single-row subquery returns more than one row

- Nun ja, eigentlich genau das, was die Meldung auch besagt...Kurz gesagt, versuchst du in EIN Feld (Datumsspalte) mehr als genau EINEN Wert zu speichern, d.h. das Resultat von (SELECT to_char(to_date(datumsspalte,'DD-MON-YY'),'DD.MM.YYYY')...etc liefert dir mehr als einen Datensatz zurück...und das passt nicht ...Das ist natürlich auch klar, da du keine 1:1 Beziehung zwischen deinen alten Datumswerten und den neuen hast....

Gruss

[ostpower]

warum denn nur ein feld? mit "set datumspalte" änder ich doch ALLE Felder in dieser spalte die mit der where-klausel ausgewählt werden. Also es sollen in der datumsspalte alle werte geändert werden die gleich denen aus der spalte a der zwischentabelle sind, das sind die datums mit dem format DD-MON-YY

[dbwizard]

warum denn nur ein feld? mit "set datumspalte" änder ich doch ALLE Felder in dieser spalte die mit der where-klausel ausgewählt werden. Also es sollen in der datumsspalte alle werte geändert werden die gleich denen aus der spalte a der zwischentabelle sind, das sind die datums mit dem format DD-MON-YY

Nein. Dieser Teil deiner Abfrage :

(SELECT to_char(to_date(datumsspalte,'DD-MON-YY'),'DD.MM.YYYY')

FROM ursprungstabelle

where exists (

select a

from zwischentabelle

where ursprungstabelle.datumsspalte=zwischentabelle.a

))

...gibt dir für jeweils eine Zeile der Ursprungstabelle mehr als EINEN Wert zurück...Wie soll die DB wissen, welchen wert aus der Subquery nun in die Ursprungsspalte geschrieben werden soll ?

[ostpower]

Ich steh grad aufm schlauch, also wie soll ich des dann denn sonst lösen?

also ich hab in der datumsspalte der ursprungstabelle folgende werte stehn:

Datum

------

12.10.2006

12.10.2006

14-MAR-07

20-JUL-05

20-JUL-05

20-JUL-05

11.06.2006

31.03.2007

Und ich möchte alle Werte mit dem Format DD-MON-YY in das Format DD.MM.YYYY umwandeln

[dr.dimitri]

In diesem Thread hab ich Dir bereits auf eine ähnliche Frage geantwortet, dass Du in Deiner Suche reguläre Ausdrücke verwenden kannst. Damit kannst Du einen Update mit der entsprechenden WHERE Bedingung schreiben.

Dim

[dbwizard]

Ich steh grad aufm schlauch, also wie soll ich des dann denn sonst lösen?

also ich hab in der datumsspalte der ursprungstabelle folgende werte stehn:

Datum

------

12.10.2006

12.10.2006

14-MAR-07

20-JUL-05

20-JUL-05

20-JUL-05

11.06.2006

31.03.2007

Und ich möchte alle Werte mit dem Format DD-MON-YY in das Format DD.MM.YYYY umwandeln

- OK :-)--... ich würde so vorgehen :

1. Neues Attribut [NeuesDatumsFeld] (oder wie auch immer du dies bezeichnen willst) in der [ursprungstabelle] : vom Typ DATE (unbedingt :-))

2. Update Ursprungstabelle SET NeuesDatumsFeld = (to_date(AltesDatumsfeld,[HierDeinGewünschtesDatumsFormat])

3. Überprüfen, ob alles korrekt im neuen Feld ist

4. Eventuell Views / Code / etc anpassen, so dass ab nun neues Feld verwendet wird.

Gruss

[ostpower]

Hm...ok und die korrekten Datums wie 01.01.2006 werden auch so übernommen und die anderen werden dann eben konvertiert?