Disintegrated Parts


#software-development #dotnet #sql

Given my background with .NET technology, and me being stupid enough to store the DateTime struct’s Ticks value in a SQL database I found the need to convert these Ticks to a DateTime again, in order to visualize the stuff going on in the database.

Let me just drop the code here. It’s not mine, but I’m having trouble finding it on the internet every time I need it.

The script to convert ticks (long value) to datetime2:

SET ANSI_NULLS ON
GO
CREATE FUNCTION dbo.ToDateTime2 ( @Ticks bigint )
    RETURNS datetime2
AS
BEGIN
    DECLARE @DateTime datetime2 = '00010101';
    SET @DateTime = DATEADD( DAY, @Ticks / 864000000000, @DateTime );
    SET @DateTime = DATEADD( SECOND, ( @Ticks % 864000000000) / 10000000, @DateTime );
    RETURN DATEADD( NANOSECOND, ( @Ticks % 10000000 ) * 100, @DateTime );
END
GO

After you created the function you can use it as:

SELECT dbo.ToDateTime2(`Table`.`Ticks`) AS `TimeStamp` FROM `Table`

The script to convert a datetime2 value to ticks again:

SET ANSI_NULLS ON
GO

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

Which can be used in the following way again:

SELECT dbo.Ticks(`Table`.`TimeStamp`) AS `Ticks` FROM `Table`

 

In case you figure out the source of these scripts, please pass me a message and I’ll give proper credit


No webmentions were found.