Konvertierung von bigint nach datetime

Question

Hallo zusammen,

mir stellt sich gerade folgendes Problem: Eine externe Software schreibt in die Datenbank meines SQL Servers 2012 einen Zeitwert in Form eines biginteger-Wertes mit 18! Stellen, z.B.: 635176165541249499

Zu diesem Thema wurde hier schonmal diskutiert, jedoch ging es dabei um einen 13-stelligen bigint-Wert bezogen auf den 1.1.1970. Bei mir ist der Bezugswert der 01.01.0001 und auf Grund der 18 Stellen handelt es sich um eine Zeitangabe mit einer Genauigkeit von einer hundertstel Nano-Sekunde.

Dadurch stellen sich mir zwei Probleme, 1) 1753 ist das früheste Datum für datetime, weshalb ich irgendwie die Datumsangabe von der Zeitangabe trennen muss, um mit date (seit 01.01.0001) arbeiten zu können; 2) datetime arbeitet nur auf 3 millisekunden genau, auch hier muss ich also mit dem exakten time-Wert rechnen.

Im Übrigen benötige ich jedoch nur eine sekundengenaue Zeitangabe. Im folgenden Codeblock habe ich das Ergebnis meiner Überlegung angefügt. Kann sich das bitte mal jemand ansehen, ob er/sie da Fehler oder unlogisches entdeckt?

Als Resultat erhalte ich für oben genannten bigint-Wert folgenden Datetime-Wert, der erstmal plausibel erscheint: 2013-10-17 14:22:34.000

SELECT
CAST(CAST(CAST(DATEADD(d,EventTime/10000000/60/60/24,CAST('01.01.0001' AS date)) AS varchar) + ' ' + 
CAST(DATEADD(s,((CAST(EventTime AS float)/10000000/60/60/24)-ROUND((CAST(EventTime AS float)/10000000/60/60/24),0,1))*24*60*60,
CAST('01.01.0001 00:00:00' AS time)) AS varchar) AS datetime2) AS datetime)
FROM table

Answer 1

Hallo Alexander,

etwas problematisch scheint mir der CAST auf float - wobei ich mehr aus dem Gefühl heraus misstrauisch bin, da Gleitkommazahlen systembedingt zu Genauigkeitsproblemen neigen - zum Nachweis fehlen jedoch Daten.

Ein alternativer Ausdruck, der darauf verzichtet und mit integer Arithmetik auskommt:

DECLARE @EventTime bigint = 635176165541249499

SELECT CAST( 
	DATEADD(ss, @EventTime % (CAST(10000000 AS bigint) * 60 * 60 * 24) / 10000000,	-- Sekunden
	DATEADD(dd, @EventTime / 10000000 / 60 / 60 / 24,  -- Tage
	CAST('00010101' AS datetime2(0))))  -- Genauigkeit in Sekunden
		AS datetime)

Gearbeitet/gerechnet wird mit DATETIME2(0) und der Cast auf DATETIME ist nur notwendig, wenn Du dies als Ergebnistyp brauchst.

Gruß Elmar

Answer 2

Hallo Elmar,

dieser Ausdruck sieht sehr gut aus, vor allem wesentlich einfacher als meiner. An diese Variante der Berechnung der Sekunden habe ich überhaupt nicht gedacht.

Vielen Dank für deine Hilfe!

Alex