How can I convert bigint (UNIX timestamp) to datetime in SQL Server ?

How can I convert bigint (UNIX timestamp) to datetime in SQL Server ?

Asked on December 24, 2018 in SQL Server.
Add Comment


  • 5 Answer(s)

    Try this simple solution,

    CREATE FUNCTION dbo.fn_ConvertToDateTime (@Datetime BIGINT)
    RETURNS DATETIME
    AS
    BEGIN
        DECLARE @LocalTimeOffset BIGINT
        ,@AdjustedLocalDatetime BIGINT;
        SET @LocalTimeOffset = DATEDIFF(second,GETDATE(),GETUTCDATE())
        SET @AdjustedLocalDatetime = @Datetime - @LocalTimeOffset
        RETURN (SELECT DATEADD(second,@AdjustedLocalDatetime, CAST('1970-01-01 00:00:00' AS datetime)))
    END;
    GO
    
    Answered on December 24, 2018.
    Add Comment

    This should works fine,

    Select
        dateadd(S, [unixtime], '1970-01-01')
    From [Table]
    

    The Epoch time from 1970-01-01 ,This is called Epoch time.

    The number of seconds that have elapsed since 00:00:00 Coordinated Universal Time (UTC), Thursday, 1 January 1970,[1][note 1] not counting leap seconds.

    Answered on December 24, 2018.
    Add Comment

    Try this code,

    SELECT
      from_unixtime(timestamp)
    FROM
      your_table
    

    http://www.w3resource.com/mysql/date-and-time-functions/mysql-from_unixtime-function.php

    Answered on December 24, 2018.
    Add Comment

    Like this

    add the Unix (epoch) datetime to the base date in seconds

    this will get it for now (2010-05-25 07:56:23.000)

     SELECT dateadd(s,1274756183,'19700101 05:00:00:000')
    Answered on January 13, 2019.
    Add Comment

    try:

    CREATE FUNCTION dbo.fn_ConvertToDateTime (@Datetime BIGINT)
    RETURNS DATETIME
    AS
    BEGIN
        DECLARE @LocalTimeOffset BIGINT
               ,@AdjustedLocalDatetime BIGINT;
        SET @LocalTimeOffset = DATEDIFF(second,GETDATE(),GETUTCDATE())
        SET @AdjustedLocalDatetime = @Datetime - @LocalTimeOffset
        RETURN (SELECT DATEADD(second,@AdjustedLocalDatetime, CAST('1970-01-01 00:00:00' AS datetime)))
    END;
    GO
    Answered on January 13, 2019.
    Add Comment


  • Your Answer

    By posting your answer, you agree to the privacy policy and terms of service.