Original von Tibor Karaszi; deutsche Übersetzung von Frank Kalis

Überblick
Zweck dieses Artikels ist es zu erklären, wie die Datetime Datentypen in SQL Server arbeiten; einschliesslich häufig auftretender Stolperfallen und allgemeinen Empfehlungen im Umgang mit ihnen.

Danksagung
Ich möchte folgenden Personen danken für ihre wertvollen Vorschläge und Input für diesen Artikel: SQL Server MVPs Steve Kass, Aaron Bertrand und Jacco Schalkwijk, Klaus Oberdalhoff und Hugo Kornelis.

Besprochene SQL Server Versionen

Dieser Artikel umfasst die SQL Server Versionen 7.0, 2000, 2005 und 2008, sofern nicht anders angegeben.

Inhaltsverzeichnis

Datum und Zeit Datentypen im SQL Server
Datum und Zeitformate
Eingabeformate für Datum und Zeit
Empfehlungen für die Eingabe
Stolperfallen und häufige Missverständnisse
Ausgabe von datetime Werten
Suchen nach datetime Werten
Entfernen des Zeitanteils
Tip: Immer den Zeitanteil auf den gleichen Wert setzen
Weitere Tips von SQL Server MVP Steve Kass
Warum ist 1753 das früheste Datum für datetime?
Referenzen und Literaturempfehlungen

Datum und Zeit Datentypen im SQL Server

Vor SQL Server 2008 stehen zwei Datentypen für Datum und Zeit Werte zur Verfügung. Da ich mich im weiteren Verlauf des Artikel sehr häufig auf sie beziehen, verwende ich Ablürzung für sie (die Spalte sn in nachfolgender Tabelle):

Beachten Sie, dass es keinen Datentypen gibt, der nur das eine oder das andere speichert. Beide der oben genannten Datentypen speichern stets sowohl einen Datumsanteil als auch einen Zeitanteil.

Geben Sie nur das Datum vor, speichert SQL Server 00:00:00.000 als Zeit. Wenn Sie nur den Zeitanteil vorgeben, speichert SQL Server den 01.01.1900 als Datum.
Da dies besonders wichtig ist, sollten Sie es erneut lesen.

SELECT CAST('20041223' AS datetime)
-----------------------
2004-12-23 00:00:00.000

SELECT CAST('14:23:58' AS datetime)
-----------------------
1900-01-01 14:23:58.000

Mit Einführung von SQL Server 2008 wurden mehrere neue Datentypen implementiert, die Datum und Zeitwerte speichern können:

• Wie man unschwer erkennen kann, sind nun endlich Nur-Datum und Nur-Zeit Datentypen verfügbar.
• DateTime2 ist ein "besserer" DateTime Datentyp aus unterschiedlichen Gründen und er verbraucht darüber hinaus auch nicht mehr Speicherplatz, sondern sogar potentiell weniger.
• Für diejenigen Datentypen, die einen Zeitanteil speichern können, kann angegeben werden, welche Bruchteil einer Sekunde man speichern will. So speichert zum Beispiel time(3) Werte wie 14:23:12.567, wobei Werte wie 14:23:12:5677 gerundet werden zu 14:23:12:568.
• Der Datentyp datetimeoffset enthält einen Zeitzonen Offset Teil.

Datums- und Zeitformate

Ein weitverbreitetes Missverständnis ist, daß SQL Server diese Datentypen in einem bestimmten lesbaren Format speichert. Dies ist nicht der Fall. SQL Server speichert solche Werte in einem internen Format (zumm Beispiel 2 Integers für datetime und smalldatetime). Trotzdem verwenden Sie in T-SQL einen Zeichenfolgenausdruck, um einen Wert zu spezifizieren (in einem INSERT Statement, zum Beispiel). Darüber hinaus gibt es Regeln, wie SQL Server verschiedene datetime String Formate interpretiert, aber SQL Server speichert nicht dieses Format in irgendeiner Form.

Datums- und Zeitformate für Eingaben

Es gibt eine ganze Reihe von verfügbaren Formaten um Datum/Zeit/DatumZeit Werte darzustellen. Einige davon sind "besser" als andere und im Verlauf des Artikel wird vielleicht deutlich, was ich mit "besser" meine. Interessanterweise stehen sämtliche Formate für sämtliche Datentypen zur Verfügung. So ist zum Beispiel ein Nur-Zeit Format auch für einen Nur-Datum Datentyp erlaubt etc. (Ich ignoriere hier bewusst den Zeitzonen Offset Teil, der nur für den datetimeoffset Typen verwendet wird - Der interessierte Leser sei hier auf SQL Server's Online hilfe BOL verwiesen)

• Man beachte, dass ANSI SQL nur ein Spezialfall des "Separated" (oder "Numeric") Formates ist, insofern als das "-", "/" und "." als Trennzeichen zulässig sind. Da dies aber das einzige im ANSI SQL Standard definierte Format ist, soll es an dieser Stelle nicht unerwähnt bleiben.
• Die Mehrzahl aller Formate erlaubt es, dass entweder der Datums- oder der Zeitanteil weggelassen werden kann, was dazu führen kann, dass das Ergebnis seltsam aussieht. So erscheint es merkwürdig, wenn man zum Beispiel '2008-08-25' als Wert für einen Zeitdatentyp angibt. Im Endeffekt ist dies aber das Gleiche, als wenn man nichts im Datumsstring angibt, ''. Beispiel:
• SELECT CAST('' AS time)
SELECT CAST('2008-08-25' AS time)

  Beide Abfragen haben das gleiche Ergebnis.
  
• Die ODBC Formate bilden eine Besonderheit insofern, als dass sie einen Marker enthalten (literal_type, t, d oder ts), der angegeben werden muss, abhängig davon ob man sowohl Datum als auch Zeit, Nur-Datum oder Nur-Zeit angibt.
• Das ISO 8601 Format erfordert sowohl ein Datum als auch ein Zeitanteil.
• SET DATEFORMAT übernimmt seine Einstellungen von SET LANGUAGE (ein explizites SET DATEFORMAT aber überschreibt SET LANGUAGE). Die Sprache ist abhängig von der Standardsprache für jedes Login, welche durch sp_configure spezifiziert wird.
• Die Regeln für das Format des Datum Teils und der neuen Typen sind verwirrend. Microsoft was bestrebt, die neuen datumbetreffenden Datentypen (date, datetime2 und datetimeoffset) weniger abhängig von diesen Einstellungen und damit mehr ANSI SQL konform zu implementieren. Das Ergebnis ist, dass diese neuen Datumtypen sprachunabhängig für eine Datumszeichenfolge sind, solange die Jahresangabe zuerst erfolgt. SQL Server muss feststellen können, dass es sich hier um ein Jahr handelt, daher wird eine 4 stellige Jahreszahl (jjjj anstelle von jj) erwartet. Ist dies der Fall, wird der String interpretiert als Jahr, dann Monat und abschliessend Tag; ohne Beachtung von DATEFORMAT oder Spracheinstellungen. Wird jedoch der Monat als Erstes angegeben, werden DATEFORMAT oder Spracheinstellungen angewendet:
  
  SET LANGUAGE British --uses dmy
GO
SELECT CAST('02-23-1998 14:23:05' AS date) --Error
GO
SELECT CAST('2/23/1998 14:23:05' AS date) --Error
GO
SELECT CAST('1998-02-23 14:23:05' AS date) --Ok
GO
SELECT CAST('1998.02.23 14:23:05' AS date) --Ok
GO
SELECT CAST('1998/02/23 14:23:05' AS date) --Ok
GO
  
  Die ersten beiden Statements laufen auf einen Fehler, da die Angabe des Jahres nicht als erstes erfolgt (und es keine 23 Monate in 1998 gab). Die letzten drei Statements scheitern deshalb nicht, weil das Jahr als erses angegeben wurde und einer der neuen Datentypen verwendet wurde.
  Alles klar? :-)

Die verfügbaren Format sind in den Books o­nline (BOL) dokumentiert, so dass ich an dieser Stelle nicht detailiert auf jedes Format eingehen möchte. Hier ist ein Link zur o­nline Version von BOL. Schauen Sie dort nach in den verschiedenen Abschnitten für die verschiedenen Formate

Beachten Sie ausserdem, dass das ODBC Nur-Zeitformat das aktuelle Datum anstelle von 1900-01-01 anzeigen, im Gegensatz zu den andere Nur-Zeit Formaten.

Einige Schlussfolgerungen:

• Das Alphabetische Format ist denkbar ungeeignet, da dieses SET LANGUAGE anhängig für alle Datentypen ist.
• Die smalldatetime und datetime Datentypen sind insofern ungünstig, als dass sie sowohl SET LANGUAGE als auch SET DATEFORMAT abhängig sind (einschliesslich der Spracheinstellungen des Logins).
• Nun mag man vielleicht denken, dass es nicht wichtig ist, ob nun eine spezielle Format/Typ Kombination SET LANGUAGE abhängig ist oder nicht, da man "niemals einen SET LANGUAGE Befehl" absetzt. Dabei sollte man jedoch beachten, das der Standard für DATEFORMAT aus den Login Spezifikationen (CREATE LOGIN etc) abgeleitet wird. Man stelle sich nun zum Beispiel eine Anwendung vor, die in den USA entwickelt wird und deren Logins die Standardsprache (us_english) und ein M/T/J Datumsformat und Zeit haben. Jetzt installiert jemand in Deutschland zum Beispiel diese Applikation und Bang! Das Programm funktioniert nicht. Habe ich nun Ihre Aufmerksamkeit? :-)

Empfehlungen für die Eingabe

Es wird Ihnen einleuchten, dass Sie keine Format/Typ Kombination verwenden sollten, welche nicht sprachneutral ist solange Sie nicht sichergestellt haben, dass die korrekten DATEFORMAT und LANGUAGE Einstellungen in Ihrer Applikationen gesetzt werden. Bedenken Sie dabei, dass das Setzen dieser Optionen innerhalb einer gespeicherten Prozedur ein Rekompilieren der Prozedur zur Laufzeit zur Folge haben kann!

Mein Empfehlung ist, ein sprachneutrales Format zu wählen. Üblicherweise verwende ich das unseparated Format, wahrscheinlich aus Gewohnheit. Das ISO 8601 Format hat den Vorteil, dass es als internationaler Standard definiert ist.

SQL Server 2008 ist just veröffentlicht, da ich dies schreibe, aber ich werde zukünftig  wahrscheinlich date, time, datetime2 und vielleicht sogar datetimeoffset verwenden.Mit der Zeit werde ich vielleicht auch das ANSI SQL Format für die neuen Datentypen verwenden, um compliant zu sein. Obwohl ich es lieber hätte, dass ANSI SQL den ISO 8601 Standard unterstützen würde. Ich werde auf Nummer Sicher gehen und entweder unseparated oder ISO 8601in den nächsten Jahren einsetzen. Selbst wenn ich mit den neuen Datentypen arbeite.

Warnungen und häufige Missverständnisse

Lassen Sie es mich noch einmal sagen, dass Sie kein Format/Typ Kombination verwenden sollten, die nicht sprachneutral ist, solange Sie nicht sichergestellt haben, dass die korrekten DATEFORMAT und LANGUAGE Einstellungen in Ihrer Applikationen gesetzt werden.

Das Numeric Format kann "-", "." oder "/" als Separator verwenden. Die Regeln nach denen SQL Server den String parsed ist unabhängig davon. Ein weitverbreitetes Missverständnis ist, dass das ANSI SQL Format (manchmal fälschlicherweise auch als "ISO Format" bezeichnet), 1998-02-23 sprachneutral für smalldatetime und datetime ist. Das ist es nicht. Es ist ein numerisches Format und als solches anhängig von den SET DATEFORMAT und SET LANGUAGE Einstellungen:

SET LANGUAGE us_english
SELECT CAST('2003-02-28' AS datetime)
Changed language setting to us_english.
-----------------------
2003-02-28 00:00:00.000

SET LANGUAGE british
SELECT CAST('2003-02-28' AS datetime)
Changed language setting to British.
Server: Msg 242, Level 16, State 3, Line 4
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Lesen Sie sich bitte einmal genau die Fehlermeldung durch. Sie beschreibt exakt, was das Problem ist. Sie geben den datetime Wert als String vor und da dieser String nicht gemäss dem Format, welches Sie verwenden, formatiert ist, kann SQL Server diesen nicht in einen datetime Wert umwandeln.

Ausgabe von Datetime Werten

Verlassen Datetime Werte den SQL Server, haben sie noch kein lesbares Format. Es obliegt der Client Applikation, diese in ein von Menschen lesbares Format umzuwandeln. Manche Applikations-/Entwicklungsumgebungen berücksichtigen die regionalen Einstellungen des Client Computers und formatieren den Wert dementsprechend. Allerdings ist dies ausserhalb der Kontrolle des SQL Servers. Sie können die Daten vom SQL Server in einem bestimmten Format zurückgeben lassen, allerdings erfordert dies, dass Sie dieses in einen String umwandeln, der resultierende Wert ist dann nicht mehr vom Datentyp Datetime.Um dies zu erreichen, verwenden Sie die CONVERT Funktion, die einen nützlichen dritten Parameter besitzt, der Sie das Format vorgeben lässt, das bei der Konvertierung verwendet werden soll. Ein paar Beispiele hierzu:

SELECT CONVERT(char(8), CURRENT_TIMESTAMP, 112)
-------- 
20040312

SELECT CONVERT(char(19), CURRENT_TIMESTAMP, 120)
------------------- 
2004-03-12 18:08:43

SELECT CONVERT(char(10), CURRENT_TIMESTAMP, 103)
---------- 
12/03/2004

Suchen nach Datetime Werten

Problem bei Suchen nach Datetime Werten ist oftmals die Tatsache, dass es sowohl einen Datums- als auch einen Zeitanteil in so einem Wert gibt. Lassen Sie uns eine Tabelle erstellen, die wir dann für einige Beispiele benutzen wollen:

CREATE TABLE #dts(c1 char(1), dt datetime)
INSERT INTO #dts (c1, dt) VALUES('a', '20040305 09:12:59')
INSERT INTO #dts (c1, dt) VALUES('b', '20040305 16:03:12')
INSERT INTO #dts (c1, dt) VALUES('c', '20040306 00:00:00')
INSERT INTO #dts (c1, dt) VALUES('d', '20040306 02:41:32')
INSERT INTO #dts (c1, dt) VALUES('e', '20040315 11:45:17')
INSERT INTO #dts (c1, dt) VALUES('f', '20040412 09:12:59')
INSERT INTO #dts (c1, dt) VALUES('g', '20040523 11:43:25')

Angenommen, wir wollen nun alle Zeilen vom 15.03.2004 suchen. Ein gebräuchlicher Fehler ist es, so danach zu suchen:

SELECT c1, dt FROM #dts WHERE dt = '20040305'

Es wird keine Zeile gefunden. Warum? Was ist passiert? Wir verwenden zwei verschiedene Datentypen in der WHERE Klausel; einmal den Datetime Datentypen (in der Datenspalte) und einen String (die rechte Seite). SQL Server konvertiert nun den einen in den anderen gemäss der dokumentierten "Datentypenpräzedenz" in BOL. Datetime hat eine höhere Präzedenz als String, also wird der String in einen Datetime Datentypen konvertiert. Da wir keinen Zeitanteil angegeben haben, wird der String in 05.03.2004 00:00:00 konvertiert. Für diesen Wert gibt es keine entsprechende Zeile in unserer Tabelle.
OK, nächster Versuch. Wir benutzen die CONVERT Funktion auf der Datenspalten Seite und konvertieren in einen String, um nun dem String Format auf der rechten Seite zu entsprechen

SELECT c1, dt FROM #dts WHERE CONVERT(char(8), dt, 112) = '20040305'

Zwar gibt dies die erwarteten Zeilen zurück, aber nun haben wir ein anderes Problem. Da wir eine Berechnung auf der Datenspalten Seite (durch die CONVERT Funktion) vorgenommen haben, kann SQL Server keinen Index verwenden, der die Suche nach diesem Kriterium unterstützt. Das kann katastrophal für die Performance sein! Also versuchen wir nun BETWEEN:

SELECT c1, dt FROM #dts WHERE dt BETWEEN '20040305' AND '20040306'

Da BETWEEN aber inklusive arbeitet, werden uns die Zeile vom 06.03.2004 00:00:00 ebenfalls zurückgegeben. Versuchen wir nun, dies in den Griff zu bekommen:

SELECT c1, dt FROM #dts WHERE dt BETWEEN '20040305' AND '20040305 23:59:59.999'

Was? Wir erhalten immernoch die Zeile mit dem 06.03.2004 00:00:00 zurück. Warum?
Schauen wir uns erneut die Definition dieses Datentypen an. Die Genauigkeit für den Datetime Datentypen ist 3.33 ms. Dies bedeutet, dass 05.03.2004 23:59:59.999 gerundet wird auf 06.04.2004 00:00:00.000. Nicht gerade das, was wir erwartet haben. Um den Rundungsproblem zu entgehen, können wir folgendes probieren:

SELECT c1, dt FROM #dts WHERE dt BETWEEN '20040305' AND '20040305 23:59:59.997'

Dies gibt zwar die erwarteten Zeilen zurück, ich würde es aber nicht als intuitiv und leicht zu lesen bezeichnen. Ist darüber hinaus der Datentype der Tabelle smalldatetime anstelle von datetime, wird der Zeitanteil wieder aufgerundet und wir sehen erneut nicht die erwarteten Zeilen. Tatsächlich haben Sie zwei Möglichkeiten, abhängig davon, ob Sie nun datetime oder smalldatetime verwenden. Ist der Datentyp smalldatetime, benutzen Sie:

SELECT c1, dt FROM #dts WHERE dt BETWEEN '20040305' AND '20040305 23:59:00'

Das Vorhandensein von zwei verschiedenen Lösungen abhängig von den verwendeten Datentypen ist nicht gerade etwas, was ich begrüsse. Und wer weiss, ob Microsoft nicht irgendwann in der Zukunft einmal, Datentypen mit grösserer Genauigkeit im Zeitanteil implementieren wird.Wir müssen erneut den Zeitteil in der WHERE Klausel adjustieren. Das folgende ist meine Empfehlung:

SELECT c1, dt FROM #dts WHERE dt >= '20040305' AND dt < '20040306'

Wir erhalten die erwarteten Zeilen zurück und es ist leicht verständlich, was das Kriterium bedeutet. Und SQL Server kann einen Index für die dt Spalte benutzen. Erweiterungen des Suchbereiches sind ebenfalls leicht vorgenommen. Angenommen, wir wollen alle Zeilen für den März 2004 zurückgeben:

SELECT c1, dt FROM #dts WHERE dt >= '20040301' AND dt < '20040401'

Die gleiche Logik. Hier ist ein weitere Ansatz, um nach allen Zeilen eines bestimmten Monats zu suchen:

SELECT c1, dt FROM #dts WHERE DATEPART(year, dt) = 2004 AND DATENAME(month, dt) = 'March'

Zwar gibt dies die erwarteten Zeilen zurück, es gibt aber zwei Probleme. Da wir wieder eine Kalkulation mit der Datumspalte durchführen, kann SQL Server keinen Index auf dieser Spalte verwenden, um die Abfrage zu unterstützen. Und die DATENAME Funktion ist anhängig von SET LANGUAGE.

Wie man den Zeitanteil entfernt

Es kommt sehr häufig vor, dass Sie nur mit dem Datumsanteil arbeiten wollen und deshalb den Zeitanteil entfernen wollen. Wie wir gesehen haben, ist dies unmöglich, wenn Ihr Rückgabewert vom smalldatetime, datetime, datetime2 oder datetimeoffset Datentypen sein soll. Verwenden Sie hingegen den date Datentypen, haben Sie keine Probleme.Schliesslich ist dieser Datentyp genau dafür konzipiert. Bestehen Sie hingegen auf die Verwendung von smalldatetime, datetime, datetime2 oder datetimeoffset können Sie hingegen aber den Zeitanteil auf 00:00:00 setzen, was genausogut ist. Hier meine Empfehlung für einen Ausdruck der einen String zurückgibt, welcher (implizit) in einen Datetime Wert konvertiert werden kann, unabhängig von SET LANGUAGE or DATEFORMAT:

CONVERT(char(8), CURRENT_TIMESTAMP, 112)

Wie Sie sehen, verwende ich die CONVERT Funktion mit dem Konvertierung Code 112, welcher das Datum im unseparated Format als String zurückgibt. Und wie Sie wissen, wird das unseparated Format nicht unter Beachtung von Datums- oder Spracheinstellungen interpretiert, wenn in einen Datetime Wert umgewandelt wird. Wir umschliessen das obige Statemtent mit CAST, um wieder in einen Datetime zu konvertieren:

SELECT CAST(CONVERT(char(8), CURRENT_TIMESTAMP, 112) AS datetime)

Für den Fall, dass Sie tatsächlich nur das Datum und nicht die Zeit an die Client Applikation zurückgeben wollen, müssen Sie stattdessen nur den String zurückgeben. Nochmals, lesen Sie die verschiedenen Konvertierung Codes für die CONVERT Funktion durch. Wahrscheinlich finden Sie ein Format, welches Sie verwenden können.

Hugo Kornelis schlug eine andere Methode vor, den Zeitanteil auf 00:00:00 zu setzen. Diese Idee beruht auf der Verwendeung eines Basisdatums (jedes gültige Datum kann hierfür verwendet werden) und Integerarithmetik (Anmerkung des Übersetzers: Tibor drückt sich an dieser Stelle unglücklich aus. Deswegen habe ich dies etwas präzisiert. Im Original steht: The idea is to decide on a base date (any date within SQL Server's datetime range will do), specify it so that time portion is 00:00:00.)
Zuerst nimmt man die Differenz zwischen Heute und diesem Basisdatum in Tagen und addiert dann diese Zahl zum Basisdatum hinzu. Der Algorithmus lautet also. [Heute]-[Basisdatum]+[Basisdatum], oder in T-SQL ausgedrückt (unter der Verwendung von '20040101' als Basisdatum).

SELECT DATEADD(day, DATEDIFF(day, '20040101', CURRENT_TIMESTAMP), '20040101')

Ein Argument für die Verwendung dieser Methode ist der höhere Geschwindigkeit. Früher war ich der Meinung, dass der Geschwindigkeitsunterschied eher akademischer Natur ist (einige Mikrosekunden einem Test aus dem Jahr 2000 zufolge, bis ich eine E-Mail von SQL Server MVP Dan Guzman mit überraschenden Informationen erhielt. Hier ist ein Zitat aus dieser Mail:

"I've found that the DATEADD technique significantly outperforms the string method when the expression is applied to a column in large queries. For example, I often use the DATEADD method to group by date when the datetime column includes a time component. I haven't tested the performance recently but I recall it was minutes in some cases."

Dan ist jemand, man ich sehr vertraue, daher denke ic, das es an der Zeit ist, umzulernen und die DATEADD Methode anstelle der String Methode zu verwenden. Dan hat auch ein Skript zur Verfügung gestellt, dass sehr schön den Unterschied verdeutlicht. Hier ist es (ich habe einige kleinere Änderungen an der Formatierung vorgenommen). Meine Tests zeigten konsistente Ergebnisse mit SQL Server 2005 und SQL Server 2008 wo die DATEADD Methode für 10.000.000 Zeilen 6 Sekunden brauchte, während die String Methode fast 13 Sekunden benötigte. Dan sprach von sogar noch grösseren Unterschied in SQL Server 2000.

Zusätzlich hat die DATEADD Methode den Vorteil der Flexibilität. Anstatt nun Hugo's Vorschläge abzutippen, geben ich hier den Originaltext von Hugo's Mail wieder:

"Die Methode, die die String Konvertierung verwendet kann nur verwendet werden, um den Zeitanteil aus einem Datum zu entfernen. Die DATEADD(DATEDIFF Methode hingegen kann sehr leicht verändert werden, um z.B. den Monats-, Quartals- oder Jahresanfang zu ermitteln. SELECT DATEADD(month, DATEDIFF(month, '20000101', CURRENT_TIMESTAMP), '20000101'). Oder um nur die Minuten und Sekunden oder nur die Sekunden zu entfernen. SELECT DATEADD(hour, DATEDIFF(hour, '20000101', CURRENT_TIMESTAMP), '20000101') (Achtung - wenn man mit Minuten arbeitet, kann die DATEDIFF Funktion einen Überlauf des Integer Bereiches verursachen - die Datumskonstante muß dann in diesen Fällen entsprechend angepasst werden.  Weitere Einsatzgebiete sind ebenfalls denkbar.  Wie erhält man den letzten Tag des Vormonats? Und wie den letzten Tag des aktuellen Monats?

SELECT DATEADD(month, DATEDIFF(month, '20000101', CURRENT_TIMESTAMP), '19991231') 
SELECT DATEADD(month, DATEDIFF(month, '20000101', CURRENT_TIMESTAMP), '20000131')

Wie erhält man das morgige Datum (ohne Zeit)?

SELECT DATEADD(day, DATEDIFF(day, '20000101', CURRENT_TIMESTAMP), '20000102')

Wie rundet man auf die nächste volle Stunde, auf den nächsten vollen Tag?

SELECT DATEADD(hour, DATEDIFF(hour, '20000101', DATEADD(minute, 30, CURRENT_TIMESTAMP)), '20000101') 
SELECT DATEADD(day, DATEDIFF(day, '20000101', DATEADD(hour, 12, CURRENT_TIMESTAMP)), '20000101')

Wie erhält man den letzten Freitag, ohne eine Kalendartabelle zu verwenden und ohne von der aktuellen DATEFIRST Einstellung abhängig zu sein?

SELECT DATEADD(day, (DATEDIFF (day, '20000107', CURRENT_TIMESTAMP) / 7) * 7, '20000107')

oder

SELECT DATEADD(day, (DATEDIFF (day, '20000108', CURRENT_TIMESTAMP) / 7) * 7, '20000107')

Das erstere gibt den aktuellen Tag zurück, wenn man es an einem Freitag laufen läßt, das letztere gibt den letzten Freitag davor zurück.

Hat man erst einmal das generelle Prinzip verstanden, ist es einfach, neue Variationen zu finden, die in bestimmten Situationen nützlich sein können.

Tip: Setzen Sie die Zeit immer auf den gleichen Wert

Falls Sie der Zeitanteil überhaupt nicht interessiert, können Sie diesen auf einen Standardwert für alle Zeilen setzen. Ich empfehle 00:00:00.000. Angenommen, Sie wollen den Wert auf das aktuelle Datum setzen, wenn ein INSERT ausgeführt wird. Hierfür können Sie eine DEFAULT Einschränkung verwenden:

CREATE TABLE #dts(id INT IDENTITY, d_date DATETIME DEFAULT CONVERT(char(8), CURRENT_TIMESTAMP, 112))
INSERT #dts DEFAULT VALUES
SELECT * FROM #dts

Ich verwende den Code 112 in der CONVERT Funktion, der mir nur das Datum in einem String im unsepareted Format zurückgibt. Trotzdem haben wir uns noch nicht davor geschützt, dass jemand den Wert explizit vorgibt und vielleicht den Zeitanteil auf etwas anderes als 00:00:00:000 setzt. Hiervor können wir uns durch eine CONSTRAINT absichern:

ALTER TABLE #dts ADD CONSTRAINT dts_chk_d_date CHECK(CONVERT(char(12), d_date, 114) = '00:00:00:000')

Welchen Vorteil haben wir dadurch? Wollen wir jetzt nach allen Zeile für ein bestimmtes Datum suchen, können wir folgende WHERE Klausel schreiben:

WHERE dt = '20040305'

Dies mag leichter zu lesen und zu verstehen sein. Angenommen wir wollen zwei Tabellen per JOIN auf dem Tag verbinden, können Sie dies nun direkt machen. Hätten Sie einen variierenden Zeitanteil, müssten Sie CONVERT in irgendeiner Form in der JOIN Operation verwenden, was wiederum fatal für die Performance sein kann. Die oben beschriebene Technik kann auch verwendet werden, falls Sie nur am Zeitanteil interessiert sind. In diesem Fall empfehle ich, dass Sie das Datum stets auf den 01.01.1900 setzen.

Ist es nicht grossartig, das wir mit SQL Server 2008 endlich getrennte Datum- und Zeit Datentypen haben?

Weitere Tips von SQL Server MVP Steve Kass

Die Übervorsichtigen können alle Daten in Abfragen als Smalldatetimes durch CAST oder CONVERT definieren. Beachten Sie jedoch, dass dies zu unerwarteten Ergebnissen bei (Nicht Small) Datetime Spalten in SQL Server 7.0 führen kann.

Ohne wirklich guten Grund, sollten Sie Daten stets als Datetime oder Smalldatetime speichern und nicht als Strings. Müssen Daten als Strings gespeichert werden (tatsächlich gibt es manchmal einige, wenige gute Gründe dafür), sollten Sie ein sprachneutrales Format wählen und ein 24 Stunden Zeitformat, falls die Zeit ebenfalls gespeichert werden soll, so dass die String überall einsetzbar sind und stets in der Art und Weise sortiert werden, wie Daten auch sortiert werden. Zum Beispiel verwenden Sie "dt < '20030101'" in der WHERE Klausel und die Abfrage gibt nicht die erwartete Zeilen zurück da dt in einem String im Format yyyy-mm-dd gespeichert ist.

Das Referenzdatum im Enterprise Manager (und anderen Microsoft Tools und Applikationen) ist 30.12.1899. EM sollte niemals für Dateneingaben verwendet werden, und selten, wenn überhaupt um Daten zu betrachten. Verwenden Sie EM um Nur-Zeit Daten zu spezifizieren, speichert EM als Datum 30.12.1899. Und wenn Sie unbedingt EM verwenden wollen, verwenden Sie explizit das Datum 01.01.1900. Beachten Sie, dass EM nur dann nur die Zeit anzeigt, wenn das Datum 30.12.1899, nicht unbedingt das, was Sie erwarten, wenn Sie 01.01.1900 als Standardwert für das Datum verwenden.

Warum ist 1753 das früheste Datum für Datetime?

Gute Frage. Dies hat historische Gründe. In der westlichen Welt kennen wir zwei verschiedene Kalender: Den Julianischen und den Gregorianischen Kalender. Diese Kalender differierten um Tage, je nachdem welches Jahrhundert Sie betrachten. Wenn also eine Kultur, die den Julianischen Kalender verwendete, den Schritt zum Gregorianischen Kalender machte, machte Sie einen Schritt von 10 bis 13 Tagen. Grossbritannien machte diesen Schritt in 1752 (Auf den 02.09.1752 folgte der 14.09.1752). Die Begründung von Sybase 1753 als frühestes Datum zu verwenden war, dass wenn Sie so ein frühes Datum speichern mussten, Sie das Jahrhundert kennen und ebenfalls diesen Sprung von 10 bis 13 Tagen handhaben können. Daher entschieden sie, keine früheren Daten als 1753 zuzulassen. Beachten sie dennoch, dass andere Länder diesen Schritt nach 1752 vollzogen. Die Türkei zum Beispiel, tat dies erst in 1927.
Als Schwede finde ich es lustig, dass Schweden die merkwürdigste Implementierung vollzog. Die Schweden entschlossen sich, den Schalttag über einen Zeitraum von 40 Jahren zu ignorieren (von 1700 bis 1740). Während dieser 40 Jahre war Schweden weder im Einklang mit dem Gregorianischen noch dem Julianischen Kalender. Aus irgendeinem Grund wurden die Schalttage in 1704 und 1708 aber nicht ausgelassen, so dass in 1712 (einem Schaltjahr) die Schweden einen Extratag einbauten (Stellen sie sich mal vor, am 30 Februar geboren zu sein!), um dann den Schritt in 1753 endgültig zu vollziehen.

Referenzen und weiterführende Literatur

Dies ist die bei weitem ausführlichste Dokumentation über Kalender, die ich bisher gefunden habe:
Frequently Asked Questions about Calendars, by Claus Tøndering

Das Buch "Developing time-oriented database applications in SQL" von Richard T. Snodgrass enthält eine Menge von Informationen darüber, wie Sie Zeit-orientierte Informationen in einem Datenmodell repräsentieren. Und, natürlich, wie Sie diese zusätzlichen (historischen) Informationen in Ihren SQL Abfragen verwenden können. Dieses buch ist nicht mehr im Handel erhältlich, man kann aber die PDF Datei kostenlos von seiner Homepage runterladen.

Den Originalartikel finden Sie hier