andy1987
Goto Top

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 face-smile

Content-ID: 301177

Url: https://administrator.de/forum/sql-timestamp-aus-ad-in-datum-wandeln-301177.html

Ausgedruckt am: 25.12.2024 um 02:12 Uhr

ukulele-7
ukulele-7 07.04.2016 um 14:27:08 Uhr
Goto Top
Also wenn ich
SELECT	cast(cast(635830039037706000 AS FLOAT) / 864000000000 - 109205 AS DATETIME)
ausführe erhalte ich 3615-11-15 09:31:43.770, passt irgendwie nicht.
Andy1987
Andy1987 07.04.2016 um 14:34:10 Uhr
Goto Top
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.
ukulele-7
ukulele-7 07.04.2016 aktualisiert um 15:16:10 Uhr
Goto Top
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?
Andy1987
Andy1987 07.04.2016 um 15:29:25 Uhr
Goto Top
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,
Andy1987
Andy1987 07.04.2016 aktualisiert um 15:41:27 Uhr
Goto Top
Dein Wert scheint richtig zu sein mit 693595. Habe 4 User vergleichen und es scheint zu passen.
ukulele-7
ukulele-7 08.04.2016 um 08:25:43 Uhr
Goto Top
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.
Andy1987
Andy1987 08.04.2016 um 08:42:45 Uhr
Goto Top
Hoffe mal das es passt face-smile 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 face-smile
ukulele-7
ukulele-7 08.04.2016 aktualisiert um 09:09:52 Uhr
Goto Top
Wenn man den genauen Rechenweg kennt wäre das sicherlich eleganter face-smile 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.