8
SQL - Timestamp aus AD in Datum wandeln
Guten Tag,
ich verzweifel gerade an einer SQL-Abfrage in der ich einen Timestamp aus dem AD in ein Datum wandeln möchte.
Mein Ursprungsfeld ist ein nvarchar.
Der Wert den ich wandeln möchte ist folgender: 635830039037706000
Zielwert ist 13.11.15
In Excel habe ich es hinbekommen. Hier für habe den Ursprungswert mit dieser Formel umgerechnet "Wert/(8,64*10^11) - 109205" und anschließend in dem Format Datum TT.MM.JJ gewandelt.
Ich scheitere aber daran gerade in SQL kläglich.
Versuche es derzeit mit folgender Abfrage:
CAST((CONVERT(float,Timestamp) / 864000000000 - 109205) AS datetime) AS LastLogon
Ich erhalte jedoch folgende Fehlermeldung:
Meldung 8115, Ebene 16, Status 2, Zeile 1
Arithmetischer Überlauffehler beim Konvertieren von expression in den datetime-Datentyp.
Die Abfrage scheint auch prinzipiell zu laufen, jedoch gibt es Fehler die Leer sind, sprich Accounts ohne Anmeldung.
Bei diesen Werten scheint er zu abzubrechen.
Muss dazu sagen, dass dies auch mit das komplizierteste ist was ich bisher in SQL gemacht habe.
Könnt ihr einem Ahnungslosen hierbei helfen? Danke schön für eure Mühe
ich verzweifel gerade an einer SQL-Abfrage in der ich einen Timestamp aus dem AD in ein Datum wandeln möchte.
Mein Ursprungsfeld ist ein nvarchar.
Der Wert den ich wandeln möchte ist folgender: 635830039037706000
Zielwert ist 13.11.15
In Excel habe ich es hinbekommen. Hier für habe den Ursprungswert mit dieser Formel umgerechnet "Wert/(8,64*10^11) - 109205" und anschließend in dem Format Datum TT.MM.JJ gewandelt.
Ich scheitere aber daran gerade in SQL kläglich.
Versuche es derzeit mit folgender Abfrage:
CAST((CONVERT(float,Timestamp) / 864000000000 - 109205) AS datetime) AS LastLogon
Ich erhalte jedoch folgende Fehlermeldung:
Meldung 8115, Ebene 16, Status 2, Zeile 1
Arithmetischer Überlauffehler beim Konvertieren von expression in den datetime-Datentyp.
Die Abfrage scheint auch prinzipiell zu laufen, jedoch gibt es Fehler die Leer sind, sprich Accounts ohne Anmeldung.
Bei diesen Werten scheint er zu abzubrechen.
Muss dazu sagen, dass dies auch mit das komplizierteste ist was ich bisher in SQL gemacht habe.
Könnt ihr einem Ahnungslosen hierbei helfen? Danke schön für eure Mühe
Share on Facebook
Share on Twitter
Share on Reddit
Share on LinkedinPlease also mark the comments that contributed to the solution of the article
Content-Key: 301177
Url: https://administrator.de/contentid/301177
Printed on: April 25, 2024 at 08:04 o'clock
8 Comments
Latest comment
Also wenn ich
ausführe erhalte ich 3615-11-15 09:31:43.770, passt irgendwie nicht.
SELECT cast(cast(635830039037706000 AS FLOAT) / 864000000000 - 109205 AS DATETIME)
Hi,
Laut AD ist der 13.11.15 09:13:49 richtig.
In Excel wird es auch sauber ausgegeben, in SQL habe ich zwei Tage unterschied. Ist mir vorher gar nicht aufgefallen. Sorry das war mein Fehler.
In Excel habe ich das Datum allerdings auch im Format TT.MM.JJ ausgegeben, da ist mir das mit der 36XX auch nicht aufgefallen.
Laut AD ist der 13.11.15 09:13:49 richtig.
In Excel wird es auch sauber ausgegeben, in SQL habe ich zwei Tage unterschied. Ist mir vorher gar nicht aufgefallen. Sorry das war mein Fehler.
In Excel habe ich das Datum allerdings auch im Format TT.MM.JJ ausgegeben, da ist mir das mit der 36XX auch nicht aufgefallen.
Die Korrektursumme müsste eher bei 693595 liegen, geht aber auch nicht auf. Wo hast du denn 109205 als Korrektur oder die genaue Formel her?
Die gabs im Netz... Da es in Excel auch gut aussah... naja
Der richtige Korrekturwert müsste 693540 sein. Zumindest ist ein Wert jetzt richtig.
meine Formel sieht mittlerweile so aus:
Case When Timpstamp = '' Then Null
Else (CAST((CONVERT(decimal,Timpstamp ) / 864000000000 - 693540) As datetime))
End
AS LastLogon,
Der richtige Korrekturwert müsste 693540 sein. Zumindest ist ein Wert jetzt richtig.
meine Formel sieht mittlerweile so aus:
Case When Timpstamp = '' Then Null
Else (CAST((CONVERT(decimal,Timpstamp ) / 864000000000 - 693540) As datetime))
End
AS LastLogon,
Dein Wert scheint richtig zu sein mit 693595. Habe 4 User vergleichen und es scheint zu passen.
Ich habe einfach in diesem einen Fall die Differenz berechnet, das passt aber keines Wegs mit dem Zeitstempel. Ich kann mir nicht vorstellen das dort ein Krummer Wert (693595,012439475) immer als Differenz drauf geschlagen wird, es wird vermutlich einen anderen, eleganteren Rechenweg geben.
Du musst in jedem Fall prüfen, ob deine Berechnung nach einem Schaltjahr noch passt, das kann sich irgendwann verschieben.
Du musst in jedem Fall prüfen, ob deine Berechnung nach einem Schaltjahr noch passt, das kann sich irgendwann verschieben.
Hoffe mal das es passt Sonst stehe ich vor nem Problem :D
Findest denn die Formel soweit okay oder kann man die auch eleganter bauen?
Wie gesagt, bin nicht fitteste in Sachen DBs und Abfragen
Sonst stehe ich vor nem Problem :DFindest denn die Formel soweit okay oder kann man die auch eleganter bauen?
Wie gesagt, bin nicht fitteste in Sachen DBs und Abfragen
Wenn man den genauen Rechenweg kennt wäre das sicherlich eleganter Aber viel anders machen kannst du im Moment nicht, eventuell nur mit convert() arbeiten und die Formate expliziter angeben.
Erklärungen gibt es ja einige:
http://www.experts-exchange.com/articles/811/Converting-Active-Director ...
http://johanmachielse.blogspot.de/2015/05/querying-active-directory-usi ...
letzterer berücksichtigt auch Zeitzonen, beide stimmen aber nicht mit deiner Differenz überein.
Aber viel anders machen kannst du im Moment nicht, eventuell nur mit convert() arbeiten und die Formate expliziter angeben.Erklärungen gibt es ja einige:
http://www.experts-exchange.com/articles/811/Converting-Active-Director ...
http://johanmachielse.blogspot.de/2015/05/querying-active-directory-usi ...
letzterer berücksichtigt auch Zeitzonen, beide stimmen aber nicht mit deiner Differenz überein.
