CODEWITHSUNDEEP
sqlsql-serverdatetime
James Gray
James Gray

Reputation: 71

Trying to convert a decimal to a datetime in SQL Server

I have an application that is saving dates as decimals in SQL Server, I need to convert them back but I have not been able to figure out how it's done.

For November 5 2018 it converts it to 636769332000000000

For November 7 2018 it converts it to 636771060000000000

I assumed it to be a count of time intervals since a given date but nothing works. Has anyone encountered anything like this before?

Upvotes: 2

Views: 303

Answers (1)

Salman Arshad
Salman Arshad

Reputation: 272416

If it is .NET ticks you could simply extract days, seconds and nanoseconds using integer arithmetic and add them to the epoch (0001-01-01 00:00:00.00 UTC). It must be done step by step because DATEADD can only add INT values.

DECLARE @t TABLE (ticks BIGINT);
INSERT INTO @t VALUES
(636769332000000000),
(636771060000000000),
(636771060009999999);

-- current time test, requires SQL 2016+
INSERT INTO @t VALUES
(DATEDIFF_BIG(MICROSECOND, '0001-01-01', SYSUTCDATETIME()) * 10);

SELECT *, DATEADD(NANOSECOND, u, DATEADD(SECOND, s, DATEADD(DAY, d, CAST('0001-01-01' AS DATETIME2)))) AS thatdate
FROM @t AS t
CROSS APPLY (
    SELECT
        ticks / CAST(864000000000 AS BIGINT),
        ticks % CAST(864000000000 AS BIGINT) / 10000000,
        ticks % 10000000 * 100
) AS ca(d, s, u)

Result:

| ticks              | d      | s     | u         | thatdate                    |
|--------------------|--------|-------|-----------|-----------------------------|
| 636769332000000000 | 737001 | 46800 | 0         | 2018-11-04 13:00:00.0000000 |
| 636771060000000000 | 737003 | 46800 | 0         | 2018-11-06 13:00:00.0000000 |
| 636771060009999999 | 737003 | 46800 | 999999900 | 2018-11-06 13:00:00.9999999 |
| 636770194571454440 | 737002 | 46657 | 145444000 | 2018-11-05 12:57:37.1454440 |

Upvotes: 4

Related Questions