Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2012
    Posts
    3

    Unanswered: How to convert bigint to timestamp

    Hi All,

    I am new SQL Server, I have below Timestamp conversion running in Oracle how do I convert same in SQL Server

    Default date - 01/01/1970
    Application stores Date as bigint - 1326310811062

    (TIMESTAMP('01/01/1970', '00:00:00') + (1326310811062 / 1000) SECONDS) AS CREATION

    Any help would be great.

    Thanks
    Srimitta

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    DECLARE @b BIGINT = 1326310811062
    ,  @d DATETIME = '1970-01-01T00:00:00.000'
    
    SELECT
       DateAdd(ms, @b % 1000, DateAdd(SECOND, @b /  1000, @d)) AS ToTheMs
    ,                         DateAdd(SECOND, @b /  1000, @d)  AS ToTheSecond
    ,                         DateAdd(MINUTE, @b / 60000, @d)  AS ToTheSecond
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Mar 2012
    Posts
    3
    Thanks Pat,

    Your solution worked.

    Srimitta

  4. #4
    Join Date
    Mar 2012
    Posts
    3
    Hi All,

    Can anybody help me on how to convert same bigint datetime using DB2 SQL.


    Thanks
    Srimitta

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    The expression in Oracle would work in DB2, too.
    But, why did you asked about DB2 in Microsoft SQL Server forum?

    Note: Outmost parentheses are useless.
    It gave only additional complexity to the expression.
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT TIMESTAMP('01/01/1970', '00:00:00') + (1326310811062 / 1000) SECONDS AS CREATION
     FROM  sysibm.sysdummy1
    ;
    ------------------------------------------------------------------------------
    
    CREATION                  
    --------------------------
    2012-01-11-19.40.11.000000
    
      1 record(s) selected.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    unless they changed something, Oracles version of sysdummy1 is DUAL
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Yes, Brett.

    But, I wrote "The expression ...". It didn't imply that the whole query would work in Oracle.

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by tonkuma View Post
    The expression in Oracle would work in DB2, too.
    But, why did you asked about DB2 in Microsoft SQL Server forum?
    You are correct, the expression (but not the full SQL statement) should work in DB2 nicely.

    We try to herd the questions to the correct forum, but in this case the OP (original poster) split forums within a message thread... As far as I know, VBulletin can't cope with that sort of split.

    As far as I know, DBForums is the only dedicated database / SQL forum that actively encourages knowledge of multiple platforms. It can cope with this kidn of problem without missing a beat, whereas most forums would launch into a tirade at the mere thought of bridging between database products and dialects. This is what I see as our strongest point as a community.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •