Datum Format intern?

Question

Hallo,

wenn man mit SSMS z.b. in der AdventureWorks Datenbank in der Tabelle Sales.Orders das Datum in der Spalte "orderdate" anschaut wird es wie folgt angezeigt:

'2015-07-22 10:03:00.000'

Die Frage für mich ist: Ist dies auch das interne Format von SQL Server, also immer mit Bindestrich - oder ist dies nur eine Anzeige von SSMS?

Warum geht z.b. die zweite Abfrage nicht wenn man das Datum mit Bindestrich angibt?

select * from sales.orders where orderdate > '20060901'  //783 Datensätze

select * from sales.orders where orderdate > '2006-09-01'  //830 Datensätze, Where greift nicht

Was mich ebenso wundert:

Warum funktioniert Nr. 2 die Ausgabe ist im deutschen Format aber wenn ich das gleiche Datum in Nr. 3 verwende, geht es nicht.

1. select getdate();  //2015-07-22 10:03:00.000
2. select convert(varchar(10), getdate(), 104);//22.07.2015
3. select convert(varchar(10), '2015-07-22 10:03:00.000', 104);//2015-07-22

Bin für jede Erklärung dankbar.

Gruss

Hans

Answer 1

Hallo Hans,

Bei der Schreibweise YYYY-MM-DD um die interne Definition des Datentyps Date (bzw. Datetime bei dir in der Frage)

In der Hilfe zu dem Datentype Date ist gut beschrieben wie sich der Datentyp zusammensetzt und welche Konventionen in der Schreibweise erlaub sind

https://msdn.microsoft.com/de-de/library/bb630352(v=sql.120).aspx

Gruß

---

Benjamin Hoch
MCSE: Data Platform,
MCSA: Windows Server 2012,

Answer 2

Hallo Hans,

welche Version der AdventureWorks Datenbank hast Du den? In den aktuellen Versionen gibt es keine Tabelle "Sales.Orders", das nächstliegende ist die Tabelle "Sales.SalesOrderHeader".

Überprüfe in der DB mal den Datentypen der Spalte "orderdate", ich tippe darauf, das die vom Typ "varchar" ist, würde das Selektionsergebnis erklären.

---

Olaf Helper

[ Blog] [ Xing] [ MVP]

Answer 3

Um die Frage wegen dem Where zu beantworten.

In der Schreibweise 20060901 wird erkannt als erster September 2006

In der Schreibweise 2006-09-01 wird erkannt als 9. Januar 2006

somit hast unterschiedliche Ergebnisse

---

Benjamin Hoch
MCSE: Data Platform,
MCSA: Windows Server 2012,

Answer 4

Hallo Olaf,

danke dir. Ich habe eine Variante der AdventureWorks die sich TSQL2012 nennt. Aber ich habe auch die AdventureWorks2008R2 Version und ja hier wäre es die Tabelle "Sales.SalesOrderHeader" mit der Spalte "orderdate"

Der Datentyp ist aber in beiden "datetime" und nicht varchar.

Warum geht es trotzdem nicht mit

select * from sales.orders where orderdate > '2006-09-01 10:03:00.000'' 

Answer 5

Hallo Benjamin

danke dir auch:

<<<

In der Schreibweise 20060901 wird erkannt als erster September 2006

In der Schreibweise 2006-09-01 wird erkannt als 9. Januar 2006

Warum ist das so? Die Ausgabe in SSMS ist doch auch in der US Schreibweise '2006-09-01 10:03:00.000'
Das müsste doch identisch sein?

Und, wie wird das Datum denn nun intern gespeichert? mit Bindestrich oder ohne?
Gruss

Hans

Answer 6

... und nochmals:

ich bekomme ja mit der Abfrage
select * from sales.orders where orderdate > '2006-09-01' 

ALLE Datensätze zurück. Das heisst die Where Filterung greift gar nicht. Selbst wenn es anstatt erster September 2006 der 9. Januar 2006 sein würde müsste ja was zurückkommen.

Answer 7

Hallo Hans,

intern wird es als numerischer Wert gespeichert, die Anzeige formatiert das Frontend, nicht SQL Server.

Welches Ergebnis bei der Konvertierung heraus kommt, ist abhängig von den Spracheinstellungen.

SET LANGUAGE DEUTSCH;
SELECT CONVERT(datetime, '20060901'),
       CONVERT(datetime, '2006-09-01'),
       CONVERT(datetime, '01.09.2006'),
       CONVERT(datetime, '01/09/2006')
GO

SET LANGUAGE ENGLISH;
SELECT CONVERT(datetime, '20060901'),
       CONVERT(datetime, '2006-09-01'),
       CONVERT(datetime, '01.09.2006'),
       CONVERT(datetime, '01/09/2006')

---

Olaf Helper

[ Blog] [ Xing] [ MVP]

Answer 8

Führe doch mal bitte diese Skript

select count(*) from sales.orders 
where orderdate > '2006-09-01'

select count(*) from sales.orders 

select min(orderdate) from sales.orders

aus und poste das Ergebnis

---

Benjamin Hoch
MCSE: Data Platform,
MCSA: Windows Server 2012,

Answer 9

Die Ausgabe ist:

830

830

2006-07-04 00:00:00.000

Answer 10

Hallo Hans,

deine Abfrage funktioniert sehr gut. Der Grund warum sie immer alle Datensätze der Tabelle liefert liegt einfach daran dass alle Datensätze die Bedingung auch erfüllen.

Du fragst die Datensätze ab welche größer als der 09.01.2006 sind, der älteste Datensatz ist aber von April 2006. Somit trifft deine Abfrage immer zu und liefer auch alle Daten.

Lege dich am besten auf genau eine Schreibweise von Datumswerten fest und behalte diese bei.

Gruß

---

Benjamin Hoch
MCSE: Data Platform,
MCSA: Windows Server 2012,

Answer 11

Lege dich am besten auf genau eine Schreibweise von Datumswerten fest und behalte diese be

Ich bevorzuge hier das ODBC Format für Datumswerte, siehe Write International Transact-SQL Statements

---

Olaf Helper

[ Blog] [ Xing] [ MVP]

Answer 12

Lege dich am besten auf genau eine Schreibweise von Datumswerten fest und behalte diese be

Ich bevorzuge hier das ODBC Format für Datumswerte, siehe Write International Transact-SQL Statements

---

...

ich würde das für Beginner nicht unbedingt empfehlen. Eigentlich auch nur in gut dokumentierten Umgebungen.

Denn beim ODBC Format wird bei der "Nur Zeit" Variante immer das aktuelle Datum verwendet.

SELECT  { t '23:23:23'}
--Im Gegensatz zu:
SELECT CAST('23:23:23' as datetime2)

Wenn man das als Basis für Berechnungen verwendet, ist das alles andere als stabil.

Ich würde zu dem allgemein üblicheren ISO-Format raten, das ist immer identisch von der Schreibweise her:

select  '2015-07-23T23:23:00'

Das wäre mein Tipp.

Und natürlich prinzipiell defensiv programmieren, das heisst auch mal an SET dmy uä denken.

---

Andreas Wolter (Blog | Twitter)
MCSM: Microsoft Certified Solutions Master Data Platform, MCM, MVP
www.SarpedonQualityLab.com | www.SQL-Server-Master-Class.com

Answer 13

Hallo Andreas,

danke für den tipp.

das T in deinem Beispiel ist so gewollt oder richtig?

'2015-07-23T23:23:00'

Gruss

Hans

Answer 14

Hallo Benjamin,

hmm, jetzt bin ich etwas verwirrt :-)

Das würde ja bedeuten dass MS SQL Server das Datum im Format

YYYY DD MM also Jahr, Tag Monat ablegt?

Ich bin davon ausgegangen das der mittlere Wert immer der Monat ist, also

<sentencetext xmlns="http://www.w3.org/1999/xhtml">YYYY-MM-DD </sentencetext>

Answer 15

Hallo Hans,

nicht ablegt sondern auswertet!

in dieser Schreibweise ist es die US Notation mit Jahr Tag Monat. Schau dir den 4 Post an da ist genau das angegeben.

---

Um die Frage wegen dem Where zu beantworten.

In der Schreibweise 20060901 wird erkannt als erster September 2006

In der Schreibweise 2006-09-01 wird erkannt als 9. Januar 2006

----

Gruß

---

Benjamin Hoch
MCSE: Data Platform,
MCSA: Windows Server 2012,

Answer 16

Danke für deine Geduld,

ja ich hatte mir deinen Post 4 schon mehrfach angeschaut.

<<In der Schreibweise 2006-09-01 wird erkannt als 9. Januar 2006

verstehe ich trotzdem noch nicht ganz, sorry

due US Notation ist doch eigentlich immer <sentencetext xmlns="http://www.w3.org/1999/xhtml">YYYY-MM-DD </sentencetext>

Die Daten in der DB in der Sales.Orders stehen ja auch im Format

2006-07-04 00:00:00.000 drin. Das ist doch sicher der 4. Juli 2006 und nicht der 7. April 2006 oder?

Gruss
Hans

Answer 17

Die Daten in der DB in der Sales.Orders stehen ja auch im Format

Hallo Hans,

wie schon ein paar Mal erwähnt wurde, speichert SQL Server Datumswerte nicht in einem Format ab, sondern als numerischer Wert; so eine Konvertierung z.B. funktioniert

SELECT CONVERT(datetime, 0)

das Anzeigeformat legt immer der Client fest. Von solchen implizierten Konvertierungen von Datumswerte würde ich immer Abstand nehmen, das kann nur schief gehen. Verwende besser eine explizite Konvertierung mittels CAST und CONVERT (Transact-SQL) mit der entsprechend Formatangabe, z.B.:

SELECT CONVERT(datetime, '2006-09-01', 120)

---

Olaf Helper

[ Blog] [ Xing] [ MVP]

Answer 18

Hallo Andreas,

danke für den tipp.

das T in deinem Beispiel ist so gewollt oder richtig?

'2015-07-23T23:23:00'

Gruss

Hans

Hallo Hans

ja, das ist so richtig. Keine Leerzeichen oder ähnlicher Firlefanz, und mit dem "T" eben eine klare Abgrenzung von Date und Time. Das ist der Sinn dahinter :-)

Das ist ISO8601, dokumentiert hier under Cast und Convert: https://msdn.microsoft.com/en-us/library/ms187928.aspx

---

Andreas Wolter (Blog | Twitter)
MCSM: Microsoft Certified Solutions Master Data Platform, MCM, MVP
www.SarpedonQualityLab.com | www.SQL-Server-Master-Class.com

Answer 19

Hallo Olaf,

das mit der internen Speicherung bzw. dem Format ist mir klar.

Was mir jedoch immer noch nicht ganz klar ist, warum er bei meinem System bzw. SSMS

2006-09-01 als 9. Januar 2006

interpretiert. Der SQL Server und auch der Client PC ist alles in Deutsch und man würde doch ein deutsches Datum nie so schreiben.

Aber egal, ich will euch hier nicht weiter nerven... :-)

VIELEN DANK AN ALLE FÜR DIE HILFE UND INFOS

Gruss

Hans

Answer 20

Dann markiere bitte die entsprechenden Antworten und die Frage als Beartwortet.

Gruß Benjamin

---

Benjamin Hoch
MCSE: Data Platform,
MCSA: Windows Server 2012,

Answer 21

Der SQL Server und auch der Client PC ist alles in Deutsch

Hallo Hans,

das ist hier egal, die für die Konvertierung relevante Spracheinstellung hängen am Login, der für die Anmeldung an den SQL Server verwendet wird; noch genauer ist es sogar die Spracheinstellung der Session; siehe mein Skript, da stelle ich die Sprache innerhalb des Batches um.

---

Olaf Helper

[ Blog] [ Xing] [ MVP]