[DATABASE/SQLSERVER] TICK → DATETIME 구하기

■ TICK 값을 DATETIME 값으로 변환하는 방법을 보여준다.

▶ TICK → DATETIME 구하기 예제 (SQL)


SELECT dbo.GetDateTime(635295744000000000) AS [DateTime];

/*
DateTime
-----------------------
2014-03-05 00:00:00.000
*/

SELECT dbo.GetDateTime(635295744000000000) AS [DateTime];

DateTime

-----------------------

2014-03-05 00:00:00.000

▶ TICK → DATETIME 구하기 (SQL)


CREATE FUNCTION dbo.GetDateTime
(
    @TickCount BIGINT
)
RETURNS DATETIME
AS
BEGIN
    DECLARE @DayCount           BIGINT;
    DECLARE @DayCountBefore1753 BIGINT;
    DECLARE @TimeTickCount      BIGINT;
    DECLARE @SecondCount        BIGINT;

    SET @DayCount           = @TickCount / CONVERT(BIGINT, 864000000000);
    SET @DayCountBefore1753 = CONVERT(BIGINT, 639905);
    SET @TimeTickCount      = @TickCount % CONVERT(BIGINT, 864000000000);
    SET @SecondCount        = @TimeTickCount / CONVERT(BIGINT, 10000000);

    RETURN DATEADD(s, @SecondCount, DATEADD(d, @DayCount - @DayCountBefore1753, CONVERT(DATETIME, '1/1/1753')));
END
GO

CREATE FUNCTION dbo.GetDateTime

(

@TickCount BIGINT

)

RETURNS DATETIME

BEGIN

DECLARE @DayCount BIGINT;

DECLARE @DayCountBefore1753 BIGINT;

DECLARE @TimeTickCount BIGINT;

DECLARE @SecondCount BIGINT;

SET @DayCount = @TickCount / CONVERT(BIGINT, 864000000000);

SET @DayCountBefore1753 = CONVERT(BIGINT, 639905);

SET @TimeTickCount = @TickCount % CONVERT(BIGINT, 864000000000);

SET @SecondCount = @TimeTickCount / CONVERT(BIGINT, 10000000);

RETURN DATEADD(s, @SecondCount, DATEADD(d, @DayCount - @DayCountBefore1753, CONVERT(DATETIME, '1/1/1753')));

END