Convertir l'object DateTime de SQL Server en BIGINT (ticks .Net)

J'ai trouvé un article CodeProject qui peut aider: Convertir DateTime en Ticks .NET en utilisant T-SQL

Je joins la fonction SQL de l'article ci-dessus (j'espère que c'est correct car il faut s'inscrire.)

 CREATE FUNCTION [dbo].[MonthToDays365] (@month int) RETURNS int WITH SCHEMABINDING AS -- converts the given month (0-12) to the corresponding number of days into the year (by end of month) -- this function is for non-leap years BEGIN RETURN CASE @month WHEN 0 THEN 0 WHEN 1 THEN 31 WHEN 2 THEN 59 WHEN 3 THEN 90 WHEN 4 THEN 120 WHEN 5 THEN 151 WHEN 6 THEN 181 WHEN 7 THEN 212 WHEN 8 THEN 243 WHEN 9 THEN 273 WHEN 10 THEN 304 WHEN 11 THEN 334 WHEN 12 THEN 365 ELSE 0 END END GO CREATE FUNCTION [dbo].[MonthToDays366] (@month int) RETURNS int WITH SCHEMABINDING AS -- converts the given month (0-12) to the corresponding number of days into the year (by end of month) -- this function is for leap years BEGIN RETURN CASE @month WHEN 0 THEN 0 WHEN 1 THEN 31 WHEN 2 THEN 60 WHEN 3 THEN 91 WHEN 4 THEN 121 WHEN 5 THEN 152 WHEN 6 THEN 182 WHEN 7 THEN 213 WHEN 8 THEN 244 WHEN 9 THEN 274 WHEN 10 THEN 305 WHEN 11 THEN 335 WHEN 12 THEN 366 ELSE 0 END END GO CREATE FUNCTION [dbo].[MonthToDays] (@year int, @month int) RETURNS int WITH SCHEMABINDING AS -- converts the given month (0-12) to the corresponding number of days into the year (by end of month) -- this function is for non-leap years BEGIN RETURN -- determine whether the given year is a leap year CASE WHEN (@year % 4 = 0) and ((@year % 100 != 0) or ((@year % 100 = 0) and (@year % 400 = 0))) THEN dbo.MonthToDays366(@month) ELSE dbo.MonthToDays365(@month) END END GO CREATE FUNCTION [dbo].[TimeToTicks] (@hour int, @minute int, @second int) RETURNS bigint WITH SCHEMABINDING AS -- converts the given hour/minute/second to the corresponding ticks BEGIN RETURN (((@hour * 3600) + CONVERT(bigint, @minute) * 60) + CONVERT(bigint, @second)) * 10000000 END GO CREATE FUNCTION [dbo].[DateToTicks] (@year int, @month int, @day int) RETURNS bigint WITH SCHEMABINDING AS -- converts the given year/month/day to the corresponding ticks BEGIN RETURN CONVERT(bigint, (((((((@year - 1) * 365) + ((@year - 1) / 4)) - ((@year - 1) / 100)) + ((@year - 1) / 400)) + dbo.MonthToDays(@year, @month - 1)) + @day) - 1) * 864000000000; END GO CREATE FUNCTION [dbo].[DateTimeToTicks] (@d datetime) RETURNS bigint WITH SCHEMABINDING AS -- converts the given datetime to .NET-compatible ticks -- see https://msdn.microsoft.com/en-us/library/system.datetime.ticks(v=vs.110).aspx BEGIN RETURN dbo.DateToTicks(DATEPART(yyyy, @d), DATEPART(mm, @d), DATEPART(dd, @d)) + dbo.TimeToTicks(DATEPART(hh, @d), DATEPART(mi, @d), DATEPART(ss, @d)) + (CONVERT(bigint, DATEPART(ms, @d)) * CONVERT(bigint,10000)); END GO 

J'ai débattu de l'opportunité de publier cela parce que cela dépend de la façon dont les dates sont stockées au niveau binary dans SQL Server, et c'est donc une solution très fragile. Pour tout autre chose qu'une conversion unique, j'utiliserais quelque chose comme la réponse que @Solution Evangelist a publiée. Pourtant, vous pourriez find cela intéressant d'un sharepoint vue académique, alors je l'afficherai quand même.

En utilisant le fait que la précision de DateTime2 correspond à la durée de tick dans .NET et que les deux sont basées sur les dates de 01-01-0001 00:00:00.0000000 de 01-01-0001 00:00:00.0000000 , vous pouvez 01-01-0001 00:00:00.0000000 DateTime en DateTime2 , puis lancer à binary(9) : 0x07F06C999F3CB7340B

Les informations datetime sont stockées RTL, inversant ainsi, nous obtiendrons 0x0B34B73C9F996CF007 .

Les trois premiers octets stockent le nombre de jours depuis 01-01-0001 et les 5 octets suivants stockent les ticks 100ns depuis minuit de ce jour, donc nous pouvons prendre le nombre de jours, multiplier par les ticks dans un jour et append les ticks représentant le time écoulé pour la journée.

Exécuter le code suivant:

 set @date = getdate() set @ticksPerDay = 864000000000 declare @date2 datetime2 = @date declare @dateBinary binary(9) = cast(reverse(cast(@date2 as binary(9))) as binary(9)) declare @days bigint = cast(subssortingng(@dateBinary, 1, 3) as bigint) declare @time bigint = cast(subssortingng(@dateBinary, 4, 5) as bigint) select @date as [DateTime], @date2 as [DateTime2], @days * @ticksPerDay + @time as [Ticks] 

renvoie les résultats suivants:

 DateTime DateTime2 Ticks ----------------------- ---------------------- -------------------- 2011-09-12 07:20:32.587 2011-09-12 07:20:32.58 634514088325870000 

Prendre le nombre de tics returnné et la conversion à un DateTime dans .NET:

 DateTime dt = new DateTime(634514088325870000); dt.ToSsortingng("yyyy-MM-dd HH:mm:ss.fffffff").Dump(); 

Récupère la date du server SQL:

2011-09-12 07:20:32.5870000

Tsk,

Voici la recommandation # 1 simplifiée, bien que je ne peux pas croire que c'était le meilleur résultat. Certes, cela est embedded, mais à ce stade, tout ce que je suis sur une date limite. Refactoring a pris less de 1 min mais peut-être cela va aider les autres qui cherchent une solution d'arrêt.

 CREATE FUNCTION [dbo].[Ticks] (@dt DATETIME) RETURNS BIGINT WITH SCHEMABINDING AS BEGIN DECLARE @year INT = DATEPART(yyyy, @dt) DECLARE @month INT = DATEPART(mm, @dt) DECLARE @day INT = DATEPART(dd, @dt) DECLARE @hour INT = DATEPART(hh, @dt) DECLARE @min INT = DATEPART(mi, @dt) DECLARE @sec INT = DATEPART(ss, @dt) DECLARE @days INT = CASE @month - 1 WHEN 0 THEN 0 WHEN 1 THEN 31 WHEN 2 THEN 59 WHEN 3 THEN 90 WHEN 4 THEN 120 WHEN 5 THEN 151 WHEN 6 THEN 181 WHEN 7 THEN 212 WHEN 8 THEN 243 WHEN 9 THEN 273 WHEN 10 THEN 304 WHEN 11 THEN 334 WHEN 12 THEN 365 END IF @year % 4 = 0 AND (@year % 100 != 0 OR (@year % 100 = 0 AND @year % 400 = 0)) AND @month > 2 BEGIN SET @days = @days + 1 END RETURN CONVERT(bigint, ((((((((@year - 1) * 365) + ((@year - 1) / 4)) - ((@year - 1) / 100)) + ((@year - 1) / 400)) + @days) + @day) - 1) * 864000000000) + ((((@hour * 3600) + CONVERT(bigint, @min) * 60) + CONVERT(bigint, @sec)) * 10000000) + (CONVERT(bigint, DATEPART(ms, @dt)) * CONVERT(bigint,10000)); END GO 

vous pouvez utiliser ci-dessous sql pour convertir une date ou utcdate en ticks

 declare @date datetime2 = GETUTCDATE() or getdate() declare @dateBinary binary(9) = cast(reverse(cast(@date as binary(9))) as binary(9)) declare @days bigint = cast(subssortingng(@dateBinary, 1, 3) as bigint) declare @time bigint = cast(subssortingng(@dateBinary, 4, 5) as bigint) select @date as [DateTime], @days * 864000000000 + @time as [Ticks] 

et utilisez ci-dessous sql pour convertir la coche à une date

 SELECT Converted = CAST(635324318540000000/864000000000.0 - 693595.0 AS DATETIME) 

EDIT: mise à jour en raison de la mise à jour des détails concernant la question initiale

Pour donner une telle précision de 100ns, vous devez exécuter l'algorithm sur SQL Server 2008 en utilisant un nouveau type de date – datetime2 dont la précision est de 100 nanosecondes. Évidemment lien vers un algorithm déjà fourni dans un autre post.

Propriété DateTime.Ticks

Une seule tique représente cent nanosecondes ou un dix millionième de seconde. Il y a 10 000 ticks en une milliseconde.

La valeur de cette propriété représente le nombre d'intervalles de 100 nanosecondes écoulés depuis minuit 00h00 le 1er janvier 0001, ce qui représente DateTime.MinValue. Il n'inclut pas le nombre de ticks atsortingbuables aux secondes intercalaires.

 DateTime dateTime = DateTime.Now; System.Int64 ticks = dateTime.Ticks; 

Une solution plus performante pourrait être: (notez que les deux premières lignes sont juste pour tester)

 DECLARE @YourDate DATETIME SET @YourDate = GETDATE() SELECT ( ( -- seconds since 1970 CAST(DATEDIFF(s,'1970-01-01 12:00:00',@YourDate) As BIGINT) ) -- seconds from 0001 to 1970 (approximate) + 62125920000 ) -- make those seconds nanoseconds * 1000000000 

Étant donné que votre date d'input ne descend qu'en secondes, nous la calculons en secondes et la multiplions par 1000000000 pour get des nanosecondes.

Il me manquait une solution one-liner millisec-précise à cette question, alors voici une:

 SELECT ROUND(CAST(CAST(GETUTCDATE() AS FLOAT)*8.64e8 AS BIGINT),-1)*1000+599266080000000000 

8.64e8 = TimeSpan.TicksPerDay / 1000
599266080000000000 = DateTime.Parse ('1900-01-01').

Cela fonctionne pour le type DATETIME mais pas pour DATETIME2. La résolution de 4/3 ms de DATETIME nécessite d'impliquer ROUND (…, -1): après la multiplication par 8.64e8 le résultat float se termine toujours par 0 ou 33.3 ou 66.6. Cela arrondit à 0, 30 ou 70.

Cette fonction renvoie l'heure unix:

 alter FUNCTION [dbo].[GETTICKS] (@datetime datetime) RETURNS bigint WITH SCHEMABINDING AS BEGIN RETURN DATEPART(millisecond,@datetime) + CONVERT(bigint, 1000 * ( DATEPART(second,@datetime) + 60 * DATEPART(minute,@datetime) + 3600 * DATEPART(hour,@datetime) + 3600 * 24 * DATEPART(DAYOFYEAR,@datetime) + convert(bigint, 3600 * 24 * (((DATEPART(year,@datetime) - 1970) * 365) + ((DATEPART(year,@datetime) - 1972) / 4))) ) ) END