Permanente Konvertierung Varchar zu date

Targa · 3. März 2014

Morgen die Herrschaften.

Ich habe diverse Spalten in denen das Datum als varchar(10) vorliegt. Ich möchte die Spalten nun als Date haben.

Da die Daten aus verschiedenen Märkten/Ländern kommen liegen die Daten zb so vor:

30.05.2003
22/12/2011
06.28.2002

Also in verschiedenen Formaten wenn man so will. Da ich im Moment nur mit 3 Märkten teste, ist es gut möglich, dass es noch mehr Unterschiede gibt.

Wie bekomm ich den Spaß nun permanent als Date?

Hab sicherlich schon 5h gegoogelt aber all die Vorschläge welche dort gemacht wurden enden bei mir mit einem Fehler.

Anzeige

Calyx · 3. März 2014

AW: Permanente Konvertierung Varchar zu date

Am einfachsten wäre es, schon auf Client-(Markt-)Seite intern das Datum ins ANSI Format zu konvertieren und als SQL-Date in die Datenbank zu schreiben. Für die Migration der vorhandenen Datensätze würde ich zuerst eine weitere Spalte (Date) in die Tabelle einfügen, einen Parser schreiben der die Dates formatiert in die neue Spalte schreibt und dann die alte VARCHAR-Spalte droppen.

Du kannst da sicher auch mit stored procedures/triggern auf DB-Ebene hin- und her konvertieren, ist aber glaube ich aufwändig(er) und macht das ganze mMn unsauber.

Targa · 3. März 2014

AW: Permanente Konvertierung Varchar zu date

Das mit der Änderung schon bei den einzelnen Märkten ist unmöglich. Zu viele Märkte auf der ganzen Welt verteilt.
Das mit der neuen Spalte hab ich auch schon gelesen wollte dies aber falls möglich vermeiden. Allerdings einmal geschrieben sollte/muss das Ganze ja automatisiert ablaufen.
Neue Daten werden/sollen in Zukunft aus csv Dateien (Je Markt eine Datei) automatisiert inkrementell eingelesen werden. Da die Dateien zwar auf einem Template aufbauen die Inhalte aber teils in landessprache und eben verschiedene Datumsformate aufweisen ist eine automatisierte "Korrektur" wohl un umgänglich. Die Frage ist eben nur wie man das am geschicktesten, einfachsten und möglichst performant lößt.
Ich persönlich hab hierzu leider bisher keinerlei Erfahrung und muss mich während des Praxissemesters eben einarbeiten, was aber ja auch der Sinn des Ganzen ist.

Stichworte oder Webseiten hierzu sind gerne gesehen

Hab nun mal folgendes probiert:

Code:

ALTER TABLE TableName ADD NewColumnName DATE;
Update TableName set NewColumnName=CONVERT(Date, [OLDColumnName],102);

Es kommt allerdings:
Conversion failed when converting date and/or time from character string.

Ohne die CONVERT Funktion kommt das gleiche:
Update TableName set NewColumnName=[OLDColumnName];

DasBoeseLebt · 3. März 2014

AW: Permanente Konvertierung Varchar zu date

Du wirst wohl um eine eigene geschrieben nicht drum rumkommen.
Dann kannst du ja anhand des Marktes, den du ja weißt?, umconvertieren.

Kolazomai · 3. März 2014

AW: Permanente Konvertierung Varchar zu date

Hallo!

Du hast zwei Probleme:
1.) Alte Daten verbessern.
2.) Neue Daten korrekt einlesen.

zu 1.)
Die Brechstangenlösung wäre dir ein kleines Script zu schreiben, das die Konvertierung vornimmt, dann alle Daten auslesen, konvertieren, und zurückschreiben bzw. in eine andere gleichartige Tabelle schreiben. Danach die alte Tabelle löschen und die neue Tabelle zur alten umbenennen. Problem dabei ist natürlich, dass du das möglicherweise nicht innerhalb deines DBVS erledigen kannst (kenne die Restriktionen nicht).
Ein andere Möglichkeit wäre, dass du dir eine neue Tabelle baust, in der du einen Trigger fürs Einfügen definierst. Der Trigger konvertiert dir den VARCHAR zu einem DATE. Danach alle Daten aus der alten Tabelle in die neue schaufeln, den Trigger arbeiten lassen und wie oben vorgehen. Problem dabei ist, dass du das wohl in SQL lösen musst (kommt auf dein DBVS an), und SQL dafür nicht sehr geeignet ist bzw. es gar nicht kann. Vorteil ist, dass der Trigger dir auch alle neuen reinkommenden Daten konvertiert und du nicht auf Lösungen wie unten beschrieben ausweichen musst.
Andere Möglichkeit ist die Problemkomplexität zu reduzieren, indem du die Daten, die im selben Format vorliegen, zuerst gruppierst (z.B. Auslagern, in analoger Lösung zu unten), oder auch temporär gruppierst. Das geht aber nur, wenn die Formate trivial genug sind oder dein DBVS irgendeine Art von fortgeschrittener Patternmatching unterstützt. Sobald du nämlich homogene Gruppen hast sollte die Konvertierung kein größeres Problem mehr sein.

zu 2.)
Was du suchst nennt sich ETL-Prozess (Extraction, Transformation, Loading), das man normalerweise aus dem Datawarehouse-Bereich kennt. Dort hat man nämlich auch verschiedene Datenquellen, die in das DWH geladen werden müssen. Beim Schritt "Transformation" erfolgt die syntaktische (Data Migration) und semantische (Data Scrubbing) Bereinigung. Bei dir handelt es sich um eine syntaktische Bereinigung, also Data Migration. Die Begriff können sich je nach Autor unterscheiden und es gibt für die beiden auch andere Anwendungsfelder als DWH, aber das sollte ein guter Ansatzpunkt zur Suche sein.
Du könntest dir z.B. eine Staging-Area aufbauen. In der wird das Datum als VARCHAR gespeichert. Du merkst dir aber auch, woher es kommt und weisst damit, welche Art von Datum vorliegt und wie es geparsed werden kann. Danach ist die Übertragung in deine tatsächliche Tabelle nicht mehr so schwierig, weil du die Daten nach Format aufteilen kannst und somit ein einheitliches Konvertierungsverfahren hast.

Mfg,

Kolazomai

Targa · 3. März 2014

AW: Permanente Konvertierung Varchar zu date

In der Theorie weiß ich was ich halbwegs machen muss. Mir fehlt es schlichtweg am praktischen Wissen wenn man es so will. Die ganze Geschichte mit dem ETL-Prozess usw. hatte ich alles in Vorlesungen allerdings eben nur die Theorie noch rein gar nichts praktisch.
Literatur oder Webseiten, sogar Stichwörter zum nach lesen würden mir da sehr helfen, vielleicht weiß ja jemand was.

Kolazomai · 3. März 2014

AW: Permanente Konvertierung Varchar zu date

Hallo!

Was gefällt dir denn an der Lösung, die ich vorgeschlagen habe, nicht?

Lösung:
1.) Skript/Programm zum Umwandeln von den Daten, die aktuell in der Tabelle sind.
2.) Beim Laden kennst du den Markt/das Herkunftsland, d.h. auch das Format, speicherst das und wandelst es beim Übertragen um (je nach DBVS, z.B. in Oracle mit TO_DATE()). Für jedes DATE-Format wirds dann halt ein extra Befehl, z.B.

Code:

INSERT INTO db (SELECT whatever, TO_DATE(dateStr, 'YYYY/MM/dd') as date FROM stagingArea WHERE dateFormat = 1);
INSERT INTO db (SELECT whatever, TO_DATE(dateStr, 'dd.MM.YY') as date FROM stagingArea WHERE dateFormat = 2);
...

Wenn du das dateFormat nicht aus dem Markt rauskriegst, dann kannst du es im einfachsten Fall so bestimmen:

Code:

UPDATE stagingArea SET dateFormat = 1 WHERE dateStr LIKE '%/%/%';
...

Warum geht das nicht?

Mfg,

Kolazomai

P.S.: Wenn das untere funktioniert, dann sollte das auch problemlos mit Triggern u.s.w. gehen und du musst keine komplizierte Lösung bauen.

Targa · 4. März 2014

AW: Permanente Konvertierung Varchar zu date

Also ich hab mir da nun mal was geschrieben für die drei Märkte:

Code:


--original Format: dd/mm/yyyy
--Converting to YYYY.MM.DD France--
Select [Contract start date] As Old_Date_Format,
CONVERT(DATE,SUBSTRING([Contract start date],7,4)
+SUBSTRING([Contract start date],4,2)
+SUBSTRING([Contract start date],1,2),102) AS New_Date_Format
from TableName;

Problem ist eben nur, dass der FormatStyle, in dem Fall 102 irgendwie keine Auswirkungen hat. Das Format ist IMMER yyyy-mm-dd, was ich aber gar nicht will.
Ist das ein komplett falscher Ansatz oder was ist da fehlerhaft?

Bzw. dann:

Code:

Select [Contract start date] AS Old_Date, case 
 ------- Start Spain -------
 when [Contract start date] like '[0-3][0-9].[0-1][0-2].[0-9][0-9][0-9][0-9]' then
 CONVERT(DATE,SUBSTRING([Contract start date],7,4) 
 +SUBSTRING([Contract start date],4,2)
 +SUBSTRING([Contract start date],1,2),102)
 ------- End Spain -------
 ------- Start France -------
 when [Contract start date] like '[0-3][0-9]/[0-1][0-2]/[0-9][0-9][0-9][0-9]' then
 CONVERT(Date,SUBSTRING([Contract start date],7,4) 
 +SUBSTRING([Contract start date],4,2) 
 +SUBSTRING([Contract start date],1,2),102)
 ------- End France -------
 ------- Start Belgium -------
 when [Contract start date] like '[0-1][0-2].[0-3][0-9].[0-9][0-9][0-9][0-9]' then
 CONVERT(DATE,SUBSTRING([Contract start date],7,4) 
 +SUBSTRING([Contract start date],1,2) 
 +SUBSTRING([Contract start date],4,2),102)
 ------- End Belgium -------
 END AS [Converted]
--Future Staging Area--
from TableName; 
--Future Staging Area--

Nur das Datum Style Format will nicht.

// Wie könnte ich den regulären Ausdruck noch verfeinern? Spanien und Belgien sind teils nicht auseinander zu halten

Calyx · 4. März 2014

AW: Permanente Konvertierung Varchar zu date

Du brauchst auf jeden Fall eine Information darüber, aus welchem Land das Datum stammt bzw. welches Format verwendet wird und musst das im Select-Statement zusätzlich als Bedingung einfügen. Ist denn sichergestellt, dass die Formate pro Land einheitlich sind? 04.03.2014 z.B. würde auf Spanien und Belgien passen. Allein am Datum kannst du nicht ermitteln, ob 4. März oder 3. April gemeint ist.

Targa · 4. März 2014

AW: Permanente Konvertierung Varchar zu date

Ja die Formate sind/sollten pro Land/Markt einheitlich (sein). Zumindest bei den dreien die ich zurzeit zu Verfügung habe, scheint es so.
Was hab ich noch für Möglichkeiten die Datumsformate zu testen, ob sie auch wirklich in dem jeweiligen angenommenen
Format vorliegen?

Es gibt noch einen dreistelligen Ländercode bei jedem Markt, den könnte man ja nehmen.

Werde es morgen mal testen.

Allerdings was mich viel mehr interessiert ist die Nichtbeachtung des Style Parameters der Convert Funktion. Hätte das Ganze nämlich schon ganz gerne in yyyy.mm.dd Format. Allerdings wird dieser schlichtweg ignoriert/nicht beachtet/Funktionslos.

Btw. Im Einsatz ist ein SQL Server 2008 R2, weswegen es die TO_DATE Funktion nicht gibt.

Kolazomai · 4. März 2014

AW: Permanente Konvertierung Varchar zu date

Hallo!

Bist du dir sicher, dass dein DBVS nicht einfach das Datum als DATE speichert und du in der Ausgabe spezifizieren sollst, wie das Format dargestellt sein soll? Also der Default zur Ausgabe von DATE ist yyyy-mm-dd und wenn du es anders als String formatiert haben willst, dann musst du wieder die CONVERT-Funktion aufrufen, z.B.

Code:

SELECT CONVERT(VARCHAR(10), dateCol, 102) AS [YYYY.MM.DD];

Der Style 102 wird bei der Konvertierung zu DATE ignoriert, weil DATE ein eigener Datentyp ist. Du musst also bei VARCHAR zu DATE keinen Style angeben, wenn das tatsächlich so ist.

Edit: Aus der MSDN API zu CONVERT:

Deine expression ist ein String/VARCHAR, folglich wird style ignoriert. Es ist also so, wie ich gesagt habe.

Mfg,

Kolazomai

Targa · 4. März 2014

AW: Permanente Konvertierung Varchar zu date

D.h ich converte varchar mit meiner obigen Funktion zu date und übertrage das ganze am besten gleich noch in eine neue Spalte. Dort liegen die Daten dann bereits als Date vor und ich kann dann via update und convert den Style Parameter anwenden?!
Hab ich das so richtig verstanden?

Kolazomai · 4. März 2014

AW: Permanente Konvertierung Varchar zu date

Hallo!

Nein, das hast du nicht richtig verstanden.

Du hast eine Spalte dateCol vom Datentyp DATE. Du konvertierst deine Datumsangaben aus der Spalte, die den Typ VARCHAR hat, um und schreibst sie in dateCol. Die Daten liegen jetzt als DATE vor. Willst du jetzt die Daten im Format yyyy.mm.dd ausgeben, dann musst du die CONVERT-Methode mit 102 als style-Parameter benutzen wie im letzten Post beschrieben.

Stell dir das so vor, dass dein DBVS DATE nicht als String speichert (dann müsste er es ja jedesmal parsen), sondern DBVS-intern-spezifisch (z.B. Binary). Für die Ausgabe, die du kriegst, wenn du ein SELECT durchführst, nimmt dein DBVS die Binary oder was auch immer Daten und konvertiert das nach dem SQL Server 2008 Standard-Date-Ausgabeformat als String.

Jetzt könntest du fragen, warum du überhaupt den Aufwand betreibst, das in ein DATE umzuwandeln. Damit haben deine Daten jetzt die richtige Semantik und du kannst deshalb bestimmte Funktionen aufrufen (>, <, ...), die du sonst nicht aufrufen könntest bzw. keinen Sinn machen würden. Außerdem kannst du dann mit irgendwelchen APIs (z.B. OR/Mappern) besser arbeiten (z.B. Date-Objekte aus der DB holen).

EDIT: Wenn du darfst/kannst, würde es sich anbieten im DBVS die Standard-Formatierung für Daten auf dein gewünschtes Format zu ändern. Das geht möglicherweise sogar nur für Statements auf die Tabelle oder noch spezifischer auf die Spalte (dateCol), für das du das willst. Ob das im SQL Server 2008 geht, weiss ich nicht. Außerdem gefährdest du damit möglicherweise plattformunabhängigkeit, ist also keine allzu saubere Lösung.

Mfg,

Kolazomai

Targa · 4. März 2014

AW: Permanente Konvertierung Varchar zu date

Ok gut, ich meinte es genauso, hab es aber falsch geschrieben.

Für die ganze DB bietet set dateformat leider eher wenig Möglichkeiten: SET DATEFORMAT (Transact-SQL)
Bezüglich der einzelnen Columns wäre das natürlich perfekt, muss ich aber noch googeln ob dies möglich ist.

Falls dies nicht möglich ist, bleibt das Format bei yyyy-mm-dd und nur durch eine Ausgabe (mit convert) kann ich auf mein gewünschtes Format yyyy.mm.dd kommen?!

Btw. du bist glaub ich die einzige Person die ich "kenne" die von DBVS redet und nicht von DBMS.

///

Select CONVERT(Varchar(10), [Test_Date], 102) AS Datum from [TableName];

Targa · 6. März 2014

AW: Permanente Konvertierung Varchar zu date

Neues Problem. Bereits in den Quelldateien sind Fehler vorhanden.
So liegt die Vertragsnummer teils mit "2345123" und einem Leerzeichen am Anfang vor.

Unten, so wie es sich gehört.

Ist ja eig. einfach zu beheben:

Code:

REPLACE(REPLACE([Contract No], '"', ''), ' ', '') AS Cleaned

Allerdings gehe ich davon aus dass das Leerzeichen kein Unicode-Codeelement 0x0020 ist und es deswegen nicht bearbeitet werden kann. -> LTRIM (SSIS Expression)

Das steht zwar nicht bei der T-SQL Funktion dabei, trotzdem gehe ich davon aus das es daran liegt. -> LTRIM (Transact-SQL)

Wie bekomm ich den Spaß nun weg?

/////////////////////////
///////////////////
Lösung: Resources: SQL Server: Remove non-printable / Unicode characters in SQL Server 2005

Nützliche Suchen

Permanente Konvertierung Varchar zu date

Videos zum Themenbereich