Vergleiche mit Datum und Zeit | Tipps Tuesday #064

[RolandSchubert]

Oft genug benötigen wir Daten aus einem bestimmten Zeitraum - einem Jahr, einem Monat oder einer Woche, manchmal sind es nur einige Stunden. Solange unsere Daten das jeweilige Datum oder sogar ein Datum mit der genauen Zeit enthalten, können wir die entsprechenden Datensätze sehr einfach herausfiltern. Damit das problemlos klappt, sollte man aber ein paar Kleinigkeiten beachten.

Schauen wir uns dazu doch einfach ein kleines Beispiel an.  Die folgenden Datensätze enthalten jeweils ein Feld [Date], das praktischerweise auch gleich den korrekten Datentyp "Date" hat:

Wir wollen jetzt die Daten aus dem Zeitraum vom 01.03.2022 bis zum 31.03.2022 herausfiltern; dazu nutzen wir natürlich das Filter Tool. Wenn wir im Basic Filter ein Feld mit dem Datentyp "Date" oder "DateTime" auswählen, wird feldtypspezifisch als Vergleichsoperator neben den "üblichen Verdächtigen"  (=, >, > usw.) auch die Option Range angeboten, zu der dann ein Start Date und ein End Date gehören. Damit ist der Bereich definiert und das Filter Tool liefert uns nur noch Datensätze aus dem gewählten Bereich (im Beispiel also RecordIDs 1 und 2).

Oft genügt aber der Basic Filter nicht, da vielleicht eine weitere Bedingung gebraucht wird oder der Zeitraum dynamischer gestaltet werden soll, indem zum Beispiel der jeweils letzte Monat gewählt wird. Das lässt sich dann nur in einem Custom Filter erledigen. Für die gerade verwendete Auswahl würde der so aussehen:

Wie könnten wir die Abfrage jetzt variabel gestalten? Die beiden "Eckdaten" sollten sich eigentlich leicht durch die entsprechenden DateTime-Funktionen ersetzen lassen. Den Anfang des Vormonats bekommen wir zum Beispiel so:

DateTimeAdd(DateTimeFirstOfMonth(), -1, 'months')

DateTimeFirstOfMonth() liefert den ersten Tag des laufenden Monats, wenn wir da mit DateTimeAdd einen Monat subtrahieren, ergibt sich der erste Tag des Vormonats. Analog würde dann 

DateTimeAdd(DateTimeFirstOfMonth(), -1, 'days') 

den letzten Tag des Vormonats zurückgeben - erster Tag des laufenden Monats minus ein Tag.

Die Daten aus dem jeweils letzten Monat würden wir entsprechend mit folgendem Vergleich bekommen:

Das Ergebnis ist allerdings etwas überraschend - der 01.03. fehlt!

Wie kann das sein? Um dieses Phänomen zu erklären, müssen wir uns etwas genauer anschauen, was (beziehungsweise welchen Datentyp) die DateTime-Funktionen liefern. Grundsätzlich liefert etwa die Konstruktion Funktion DateTimeAdd(DateTimeFirstOfMonth(), -1, 'months') einen DateTime-Wert zurück - also zum Beispiel "2022-03-01 00:00:00". Und der ist tatsächlich (zumindest technisch betrachtet) größer als "2022-03-01", so dass unsere Bedingung nicht erfüllt wird!

Das können wir vermeiden, indem wir das Ergebnis (also DateTime) in einen Date Datentyp umwandelt - mit ToDate kein Problem. Danach stimmt dann auch das Ergebnis:

Wie aber sieht es aus, wenn die gelieferten Daten ein Feld mit Datum und Zeit enthalten (also DateTime)?

Wenn wir jetzt ein Filter Tool verwenden und das DateTime-Feld auswählen, ändert sich das Bild ein wenig:

 

Zusätzlich wird jetzt die standardmäßig ausgewählte Option angeboten, nur das Datum zur Filterung zu verwenden; in diesem Fall wird beim Vergleich nur das Datum berücksichtigt, die mitgelieferte Zeit also ignoriert. Nun können wir natürlich umschalten, also Datum und Zeit verwenden:

Aber Vorsicht: Wenn wir jetzt einfach die "Date only" Option deaktivieren, wird "00:00:00" als Uhrzeit angenommen - der 31.03.2022 wäre jetzt nicht mehr dabei. Hier muss also bei der Uhrzeit "nachjustiert" werden (z.B. auf 23:59:59).

Bei der Umwandlung in einen "Custom Filter" können wir auf die Erkenntnisse zurückgreifen, die wir vorhin gewonnen haben - diesmal wird der DateTime-Datentyp angeliefert und unsere Bedingung ist "Date" - wir müssen also die gelieferten Daten in "Date" umwandeln; das geht wieder mit ToDate.

Und wenn wir nun wieder eine dynamische Auswahl brauchen? Das sollte doch ohne weitere Operationen gehen - DateTime wird geliefert, die variable Bedingung ist auch DateTime. Grundsätzlich ist das richtig - aber wir müssen doch eine kleine Anpassung vornehmen. DateTimeFirstOfMonth liefert zwar den ersten Tag des laufenden Monats, wenn wir einen Tag subtrahieren, landen wir beim letzten Tag  des Vormonats - aber um 00:00:00 h. Damit würde der letzte Tag komplett entfallen. Dieses Problem können wir mit einem kleinen Trick lösen: Wir gehen nicht einen Tag, sondern nur eine Sekunde zurück - dann erfassen wir auch den letzten Tag des Vormonats komplett.  

Alternativ können wir natürlich auch beide Seiten des Vergleichs in einen Date-Datentyp umwandeln:

Wenn wir also Datum und Zeit vergleichen wollen - und das gilt für Filter, aber genauso für Formeln - kommt es ganz wesentlich auf den genauen Datentyp an. Wenn wir unerwartete Ergebnisse vermeiden wollen, sollte der Vergleich immer auf gleiche Datentypen zugreifen - bei Vergleichen von numeric oder string Datentypen spielt das zumeist keine Rolle. Auf diese Kleinigkeit sollte man also achten, wenn man langwierige Fehlersuche vermeiden will ... und ich spreche hier aus Erfahrung.

Weitere Tipps Tuesday Beiträge

Dieser Eintrag ist Teil der Tipps Tuesday-Serie, alle Einträge dieser Serie findest du in unserem Index aufgelistet.