SQL-Abfrage / ACCESS / Datum zwischen X und Y / Formatproblem

[xound]

Hallo Forum,

direkt zum Anfang eine Frage; ich habe hier eine ACCESS-Datenbank, die ich nicht verändern darf! Das erstmal vorweg.

Darin ist eine Tabelle enthalten, wo Datumsangaben gespeichert sind.

Das Problem ist, dass die Datumsangaben verschieden formatiert sind.

In den Feldern des Datum-Attributs stehen drei verschiedene Datumsformate:

04/07/2005

12.07.2005

2005.08.08

Ich greife nun via ODBC auf die Datenbank zu und mache eine SQL-Abfrage.

Frage 1: Wie kann ich sicherstellen, dass alle Formate beachtet werden?

Frage 2: Wie sieht die Abfrage aus, die alle Datensätze mit einem Datum von ... bis ... ausgibt?

Vielen Dank!!

[m3rry]

Zuersteinmal würde ich den Erzeuger der Datenbank mal fragen was er sich dabei gedacht hat 3 unterschiedliche Datumsformate in einer Tabelle zu benutzen.

x: Wenn(Teil([Datum];3;1)="/" Oder Teil([Datum];3;1)=".";Teil([datum];7;4) & "." & Teil([Datum];4;2) & "." & Teil([datum];1;2);[Datum])

Trag obenstehendes in ein neues Feld deiner Abfrage ein, dann werden alle Datumsformate in korrekter Form dargestellt. Du musst nur noch den Eintrag [Datum] mit dem korrektem Feldnamen austauschen. Als Kriterium des Feldes trägst du folgendes ein:

zwischen [ von Datum ? ] und [ bis Datum ?]

Wenn die Abfrage geöffnet wird, erscheint nun ein Eingabefenster mit der Frage "von Datum ?" hier muß nun das Datum eingetragen werden.

Im Format "YYYY.MM.DD" Dies ist in Tabellen aus meiner Erfahrung her das Beste Format, da auch die Sortierreihenfolge richtig beachtet wird.

[xound]

Zuersteinmal würde ich den Erzeuger der Datenbank mal fragen was er sich dabei gedacht hat 3 unterschiedliche Datumsformate in einer Tabelle zu benutzen.

x: Wenn(Teil([Datum];3;1)="/" Oder Teil([Datum];3;1)=".";Teil([datum];7;4) & "." & Teil([Datum];4;2) & "." & Teil([datum];1;2);[Datum])

Trag obenstehendes in ein neues Feld deiner Abfrage ein, dann werden alle Datumsformate in korrekter Form dargestellt. Du musst nur noch den Eintrag [Datum] mit dem korrektem Feldnamen austauschen. Als Kriterium des Feldes trägst du folgendes ein:

zwischen [ von Datum ? ] und [ bis Datum ?]

Wenn die Abfrage geöffnet wird, erscheint nun ein Eingabefenster mit der Frage "von Datum ?" hier muß nun das Datum eingetragen werden.

Im Format "YYYY.MM.DD" Dies ist in Tabellen aus meiner Erfahrung her das Beste Format, da auch die Sortierreihenfolge richtig beachtet wird.

Hallo,

danke für die Antwort. Allerdings geht es um eine SQL-Abfrage via ODBC. Das heisst, dass ich Standard-SQL 92 verwende!

Ist die oben genannte Vorgehensweise in SQL umsetzbar?

[m3rry]

Hmmm .. wenn du bereits eine ODBC Verbindung auf die Tabelle hast, kannst du diese auch in Access importieren, das sei nur mal am Rande erwähnt, da du selbst nicht den erfahrendsten Eindruck was dies angeht machst, würde ich dir zum reinschnuppern auch erstmal Access empfehlen.

In reines SQL lässt sich das auch umwandeln, bin hier nicht sehr betucht, aber ca so:

SELECT DATUM "DATUM_NEU", IF SUBSTR( DATUM, 3, 1) = '/' or SUBSTR( DATUM, 3, 1) = '.' THEN SUBSTR( Datum ,7,4) & '.' &SUBSTR( Datum ,4,2) & '.' SUBSTR( Datum ,1,2) ELSE DATUM;


WHERE DATUM_NEU between '2005.01.01' and '2006.01.0.1'

Wobei ich mir mit der IF THEN ELSE Anweisung in SQL wirklich nicht sicher bin.

Jedenfalls kannst du nichts kaputtmachen beim ausführen.

[xound]

SELECT DATUM "DATUM_NEU", IF SUBSTR( DATUM, 3, 1) = '/' or SUBSTR( DATUM, 3, 1) = '.' THEN SUBSTR( Datum ,7,4) & '.' &SUBSTR( Datum ,4,2) & '.' SUBSTR( Datum ,1,2) ELSE DATUM;


WHERE DATUM_NEU between '2005.01.01' and '2006.01.0.1'

Wobei ich mir mit der IF THEN ELSE Anweisung in SQL wirklich nicht sicher bin. Jedenfalls kannst du nichts kaputtmachen beim ausführen.

Oh oh, ich glaube der verwendete Treiber unterstützt nur die Grundfunktionen.

Fatal error: Uncaught exception 'com_exception' with message 'Source: Microsoft JET Database Engine

Description: Unzulässige SQL-Anweisung; 'DELETE', 'INSERT', 'SELECT' oder 'UPDATE' erwartet.'

Hm, jetzt habe ich natürlich die Problematik, dass ich mir die verschiedenartig formatierten Datensätze nicht direkt umformen kann. Ich hatte zwar vor, einige Datensätze zunächst auszulesen und dann mit DOM via PHP in eine XML-Struktur zu pushen (für Ajax), aber eig. wollte ich mir die Umformung sparen, da ich nicht alle Datensätze in XML übernehmen wollte, sondern nur einen gewünschten.

Gut, vielleicht geht es ja so; was meinst Du dazu:

Kann ich nicht ganz simpel sagen:

SELECT date FROM tabelle WHERE date BETWEEN '01.01.2004' AND '01.01.2005' AND date BETWEEN '01/01/2004' AND '01/01/2005' AND date BETWEEN '2004.01.01' AND '2005.01.01'

??

[m3rry]

Nein, das funktioniert definitiv nicht.

Da du nur jeweil 1 Format in der Spalte hintelegt hast, nicht aber 3 Formate in einem Feld.

Falls dein SQL dem Befehl Union kennen sollte, kannst du den Select auch wie folgt aufbauen:

 


SELECT date FROM tabelle WHERE date BETWEEN '01.01.2004' AND '01.01.2005'


UNION [ALL]


SELECT date FROM tabelle WHERE date BETWEEN '01/01/2004' AND '01.01.2005'


UNION [ALL]


SELECT date FROM tabelle WHERE date BETWEEN '2005.08.01' AND '2005.08.30'

Mit UNION, kannst du beliebig viele Selects hintereinander ausführen, die dann zusammengeführt erscheinen.

Allerdings hast du dann wieder die falsch Formatierten Formate in deinem Ergebniss.

[m3rry]

Fatal error: Uncaught exception 'com_exception' with message 'Source: Microsoft JET Database Engine

Description: Unzulässige SQL-Anweisung; 'DELETE', 'INSERT', 'SELECT' oder 'UPDATE' erwartet.'

Übrigens scheint es eher so zu sein, das er mit dem in ' gesetztem Text nicht zurecht kommt, versuch dan nochmal ein wenig rum, eigentlich sollte es so funktionieren.

Hast du die From Klausel übehaupt eingefügt ? Ich habe hier leider kein Testsystem wo ich rumprobieren könnte, im Grunde ist der Syntax recht einfach zu verstehen ich bin mir nur bei der Formatierung nicht sicher, wann ' oder wann " verwendet wird.

Schau doch mal hier rein: SQL Grundlagen

Wie du meinen SELECT anpassen musst, damit er für dein Beispiel funktioniert.

[xound]

Fatal error: Uncaught exception 'com_exception' with message 'Source: Microsoft JET Database Engine

Description: Unzulässige SQL-Anweisung; 'DELETE', 'INSERT', 'SELECT' oder 'UPDATE' erwartet.'

Übrigens scheint es eher so zu sein, das er mit dem in ' gesetztem Text nicht zurecht kommt, versuch dan nochmal ein wenig rum, eigentlich sollte es so funktionieren. Hast du die From Klausel übehaupt eingefügt ? Ich habe hier leider kein Testsystem wo ich rumprobieren könnte, im Grunde ist der Syntax recht einfach zu verstehen ich bin mir nur bei der Formatierung nicht sicher, wann ' oder wann " verwendet wird. Schau doch mal hier rein: SQL Grundlagen Wie du meinen SELECT anpassen musst, damit er für dein Beispiel funktioniert.

$mdb->execute('SELECT property_name, value "DATUM_NEU", IF SUBSTR( value, 3, 1) = "/" or SUBSTR( value, 3, 1) = "." THEN SUBSTR( value ,7,4) & "." &SUBSTR( value ,4,2) & "." SUBSTR( value ,1,2) ELSE value; FROM Properties WHERE DATUM_NEU between "2005.01.01" and "2006.01.01"');

So sieht die Abfrage aus; die FROM-Klausel ist doch richtig plaziert, oder?

[xound]

Der Fehler ist im Übrigen ein anderer, undefinierbar ..

Fatal error: Uncaught exception 'com_exception' with message 'Source: Unknown

Vielleicht doch ein Problem mit der If Then Else Struktur?

[xound]

Ich habe das mit dem Union gerade mal ausprobiert.

Was mir dabei aufgefallen ist, ist, dass die Ergebnismenge nicht korrekt ist.

Das Feld in dem die Datumsangaben stehen ist ein einfaches Textfeld (Da darin auch andere Angaben ausser dem Datum stehen können).

Deswegen wahrscheinlich auch die verschiedenen Formatierungen, z.B. per Hand eingetragen ...

Funktioniert "between" überhaupt bei einem Textfeld?

SELECT value FROM Properties WHERE value BETWEEN "04.07.2005" AND "16.10.2005"

gibt mir jedenfalls

When (Date) 04/03/2006 ??

When (Date) 06/02/2006 ??

When (Date) 06/02/2006 ??

When (Date) 12.07.2005

When (Date) 16.07.2005

When (Date) 04.08.2005

When (Date) 08.08.2005

When (Date) 12.08.2005

When (Date) 04.10.2005

When (Date) 08.10.2005

When (Date) 16.10.2005

...

Sehr verwirrend zugegebenermaßen ... Vielleicht hat ja jemand eine Idee?

Danke!!

[Pinhead]

Wenn ich das richtig verstanden habe gibt es also in der DB ein Textfeld das Datumswerte in verschiedenen Formaten enthält. Diese Feld kann aber auch andere Daten enthalten.

Dann würde ich eine Abfrage in folgender Art definieren

SELECT value 

FROM properties

WHERE value BETWEEN "04.07.2005" AND "16.10.2005"

OR value BETWEEN "04/07/2005" AND "16/07/2005"

OR value BETWEEN "2005.07.04" AND "2005.10.16";

Allerdings werden dann, sollten sich die Benutzer mal wieder ein neues Datumseingabeformat ausdenken diese Datensätze nicht erwischt

[lesezeichen]

Wenn ich das richtig verstanden habe gibt es also in der DB ein Textfeld das Datumswerte in verschiedenen Formaten enthält. Diese Feld kann aber auch andere Daten enthalten.

Dann würde ich eine Abfrage in folgender Art definieren

SELECT value 

FROM properties

WHERE value BETWEEN "04.07.2005" AND "16.10.2005"

OR value BETWEEN "04/07/2005" AND "16/07/2005"

OR value BETWEEN "2005.07.04" AND "2005.10.16";

Dankeschön. Zunächst funktioniert das so.

Allerdings scheint das BETWEEN nicht alle betreffenden Datensätze zu liefern.

OK, mal ein Beispiel - In der Datenbank stehen die folgenden Datumsangaben (mit dem Typ Textfeld):

08/08/2005

08/08/2005

12/08/2005

27/09/2005

27/09/2005

01/09/2005

19/09/2005

19/09/2005

19/09/2005

27/09/2005

01/10/2005

04/10/2005

Wenn ich nun die folgende Abfrage mache:

SELECT value FROM properties WHERE value BETWEEN "08/08/2005" AND "04/10/2005"

Dann ist die Ausgabe lediglich:

04/10/2005

08/08/2005

08/08/2005

04/10/2005

08/08/2005

08/08/2005

Woran könnte das liegen?

Danke!!

[Pinhead]

Woran könnte das liegen?

Ich vermute, dass das System nicht weiß wie die Grenzen definiert sind. Schließlich werden ja Texte mit einander verglichen. Die Frage ist also wann liegt ein Text zwischen zwei Texten. Dies würde mich auch verwirren. :confused:

Allerdings weiß ich ja das die Texte ein Datum repräsentieren. Das ermöglicht mir eine Entscheidung zu treffen ob der Text zwischen den anderen Texten liegt oder nicht.

Also sollte der Abfrage mitgeteilt werden das es sich um ein Datum handelt.

Dies kann man mit der Funktion toDate(text,pattern) lösen.

SELECT value 

FROM properties

WHERE to_date(value,'dd.mm.yyyy') 

          BETWEEN to_date('04.07.2005','dd.mm.yyyy') 

          AND to_date('16.10.2005','dd.mm.yyyy')

OR to_date(value,'dd/mm/yyyy') 

          BETWEEN to_date('04/07/2005,'dd/mm/yyyy') 

          AND to_date('16/07/2005,'dd/mm/yyyy') 

OR ...;

Dies würde ich so schreiben wenn ich mit Oracle arbeite. Wie die Funktion bei Access heißt weiß ich nicht bin mir aber sicher das es so etwas dort gibt

[m3rry]

Es liegt daran, das die Daten als Text formatiert sind und dann noch in verschiedenen Formaten:

Beispiel aufsteigend sortiert:

13/09/2004

2005.08.01

30.01.2004

Hier stimmt Datumstechnisch nichts, aber da es Text ist, sortiert er es richtig, nach den ersten Buchstaben des Textes:

1

2

3

D.H du musst wie ich dir bereits geraten habe die Werte umformatieren ich habe dir auch ein Beispiel dazu geliefert, mehr kann ich nicht tun du hast nun alle Möglichkeiten. Schau dir den Syntax einer IF THEN ELSE Anweisung auf der SQL Seite an und dann fang langsam an zu testen dann baust du den String komplett auf.

Nochmal etwas ... mit SQL auf eine Access Datenbank mittels ODBC Treiber zuzugreifen ist unnötig. Erstell doch einfach eine eigene Access Datenbank und dann verknüpfst du die Tabelle die du benötigst in deine Datenbank und erstellst dort die passende Abfrage auf die Verknüpfte Tabelle.

[lesezeichen]

D.H du musst wie ich dir bereits geraten habe die Werte umformatieren ich habe dir auch ein Beispiel dazu geliefert, mehr kann ich nicht tun du hast nun alle Möglichkeiten. Schau dir den Syntax einer IF THEN ELSE Anweisung auf der SQL Seite an und dann fang langsam an zu testen dann baust du den String komplett auf.

Richtig, ich fürchte nur, dass das so, wie Du mir geraten hast mit dem JetDirect-Treiber nicht funktioniert.

Nochmal etwas ... mit SQL auf eine Access Datenbank mittels ODBC Treiber zuzugreifen ist unnötig. Erstell doch einfach eine eigene Access Datenbank und dann verknüpfst du die Tabelle die du benötigst in deine Datenbank und erstellst dort die passende Abfrage auf die Verknüpfte Tabelle.

Unnötig ist das nicht, denn es geht darum via PHP direkt die ACCESS-Datenbank zuzugreifen und bestimmte (!) Daten in XML zu formatieren.

Zur Not hole ich mir auch alle Datumsangaben mit PHP raus und formatiere die dort um - ich dachte nur es gäbe einen einfacheren Weg.

[m3rry]

Hmmmm .. ok verstehe hatte das vorher nicht ganz mitbekommen, entschuldige.

Dann versuch doch mal folgendes:

SELECT SUBSTR( DATUM, 3, 1)


wenn das funktioniert:


SELECT SUBSTR( DATUM, 3, 1) & "5"

oder

SELECT SUBSTR( DATUM, 3, 1) & '5'


Wenn das soweit funktioniert:


SELECT SELECT DATUM "DATUM_NEU" IF SUBSTR( DATUM, 3, 1) = '/' THEN 'OK'

So baust du dir den kompletten String zusammen, prüfst welcher Teil funktioniert und welcher nicht. Dann hast du am Ende wenn du mein zweites Posting betrachtest alle Datumseinträge im richtigem Format und kannst diese mit WHERE Between abfragen.

[lesezeichen]

Hmmmm .. ok verstehe hatte das vorher nicht ganz mitbekommen, entschuldige.

Dann versuch doch mal folgendes:

SELECT SUBSTR( DATUM, 3, 1)


wenn das funktioniert:


SELECT SUBSTR( DATUM, 3, 1) & "5"

oder

SELECT SUBSTR( DATUM, 3, 1) & '5'


Wenn das soweit funktioniert:


SELECT SELECT DATUM "DATUM_NEU" IF SUBSTR( DATUM, 3, 1) = '/' THEN 'OK'

So baust du dir den kompletten String zusammen, prüfst welcher Teil funktioniert und welcher nicht. Dann hast du am Ende wenn du mein zweites Posting betrachtest alle Datumseinträge im richtigem Format und kannst diese mit WHERE Between abfragen.

Danke; leider wieder ein Problem mit Microsoft.Jet.OLEDB.4.0.

Fatal error: Uncaught exception 'com_exception' with message 'Source: Microsoft JET Database Engine

Description: Undefinierte Funktion 'SUBSTR' in Ausdruck.

Das gleiche gilt im Übrigen für TO_DATE ...

Viele Möglichkeiten bleiben nicht, oder?

[Pinhead]

Wie die Funktion bei Access heißt weiß ich nicht bin mir aber sicher das es so etwas dort gibt

DateValue(Zeichenkette)

[lesezeichen]

DateValue(Zeichenkette)

Super, eine Abfrage der Art

SELECT DateValue(value) FROM properties;

spuckt schonmal alles einheitlich formatiert aus. Das bedeutet, dass die ACCESS-Funktionen wenigstens verfügbar sind.

Die Ausgabe bekommt die Spaltenüberschrift EXPR1000, die allerdings erst bei der Ausgabe ansich generiert wird.

Wie wende ich jetzt das BETWEEN an, um zu schauen, ob die richtigen Werte gefunden werden? Innerhalb des Statements habe ich ja keinen Zugriff auf value ... ?

Danke!

[lesezeichen]

So, das müsste korrekt sein:

SELECT DateValue(value) AS date_value

FROM properties

WHERE (((DateValue([value])) Between "08.08.2005" And "04.10.2005"))

ORDER BY DateValue(value);

Leider entsteht dabei wieder das Problem, dass nicht alle Datensäzte einbezogen werden.

Korrekt wäre:

08.08.2005

08.08.2005

08.08.2005

08.08.2005

12.08.2005

12.08.2005

01.09.2005

19.09.2005

19.09.2005

19.09.2005

27.09.2005

27.09.2005

27.09.2005

01.10.2005

04.10.2005

04.10.2005

Die Ausgabe der oben genannten SQL-Abfrage ist aber:

08.08.2005

08.08.2005

08.08.2005

08.08.2005

04.10.2005

04.10.2005

08.02.2006

08.02.2006

08.02.2006

08.02.2006

08.02.2006

Eigentlich sollten die Werte doch jetzt als Datumswerte erkannt werden, warum besteht noch immer das Problem ...Wie kommt überhaupt die 2006 da rein?

Vielleicht hat ja jemand noch eine Idee!

Besten Dank

[Pinhead]

Also du teilst der Abfrage jetzt mit das du die selektierten Werte als Datum bekommen möchtest. Nun musst du ihr nur noch "sagen" das deine übergebenen Texte auch vom Typ Datum sind.

Also :

SELECT DateValue(value) AS date_value

FROM properties

WHERE (((DateValue([value])) Between DateValue("08.08.2005")

 And DateValue("04.10.2005"))

ORDER BY DateValue(value);

[lesezeichen]

Also du teilst der Abfrage jetzt mit das du die selektierten Werte als Datum bekommen möchtest. Nun musst du ihr nur noch "sagen" das deine übergebenen Texte auch vom Typ Datum sind.

Also :

SELECT DateValue(value) AS date_value

FROM properties

WHERE (((DateValue([value])) Between DateValue("08.08.2005")

 And DateValue("04.10.2005"))

ORDER BY DateValue(value);

Klingt logisch, hattest Du ja eig. auch schon mit Deinem ersten Post vorgegeben (Sorry). Jedoch kommt es dann zu folgendem Fehler:

Description: Syntaxfehler (fehlender Operator) in Abfrageausdruck '(((DateValue([value])) Between DateValue("08.08.2005") And DateValue("04.10.2005")) ORDER BY DateValue(value);'.

Der Operator ist doch vorhanden (AND), oder habe ich da was übersehen?

[lesezeichen]

Dank Deiner Hilfe hat es funktioniert. (Das Problem war, dass er sich bei der Klammersetzung verhaspelt hat --> Ist jetzt gelöst)!!!

Vielen Dank!! Auch Danke an Merry für die Nervern. :-)

Ihr seid klasse!! Ich geb einen aus.

[lesezeichen]

Oops, zu früh gefreut.

Ich habe jetzt mal eine Datenbank genommen, in der neben den Datumsangaben auch andere Daten in den Value-Felder stehen (z.B. normale Personennamen, bzw. kurze Texte).

Mit dieser DB, die exakt den gleichen Aufbau hat, nur neben den Datumsangaben auch die Namen bzw. Texte kommt die folgende Fehlermeldung:

Description: Datentypen in Kriterienausdruck unverträglich.' 

Ich dachte zuerst, dass ich das mit einer expliziten Angabe in der Where-Klausel lösen kann, d.h. der Angabe, dass nur die values genommen werden sollen, die auch tatsächlich Datumsangaben entsprechen, also so:

SELECT property_name, DateValue(value) AS date_value

FROM properties

WHERE property_name="When (Date)" AND DateValue(value) Between DateValue("08.08.2005") And DateValue("04.10.2005")

ORDER BY DateValue(value)

Aber das funktioniert nicht; der oben besagte Fehler kommt ....

Das liegt vermutlich daran, dass er trotz der zusätzlichen Where-Klausel alle Felder von value in DateValue zu konvertieren versucht, oder?

Kann man das umgehen?

[Pinhead]

Kann man das umgehen?

Versuch mal die Methode aus DateValue aus der Select anweisung zu entfernen.

Also

SELECT property_name, value

FROM properties

WHERE DateValue(value) Between DateValue("08.08.2005") And DateValue("04.10.2005")

ORDER BY DateValue(value)