Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2003
    Location
    now in UAE, i am an INDIAN.
    Posts
    16

    Unanswered: Converting Bigint to Date format

    Hi all,
    We have an application from a third party vendor.
    In which a table stored time as below in bigint format.

    CREATEDTIME RESPONDEDTIME DUEBYTIME
    -------------------- -------------------- --------------------
    1236666082555 1236666085435 1236680482555


    And it is diplaying as below through their application

    Created date : 10 Mar 2009 14:21:22
    Responded Date : 10 Mar 2009, 10:21:25
    Due date : 10 Mar 2009 14:21:22

    Any idea how they are converting into this format?

    --thanks for your time...
    TEAM (Together Everybody Achieve More)

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I'm unable to test this ATM but as a guess:
    Code:
    DATEADD(ss, CREATEDTIME, '19700101')
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by ipmurali
    Any idea how they are converting into this format?
    those numbers are unix epoch numbers with milliseconds as the lowest three digits

    in other words, 1236666082555 actually represents 1236666082 (number of seconds since midnight jan 1 1970), with an additional 555/1000 seconds

    by the way, 1236666082 is 2009-03-10 06:21:22 UTC

    if you want to convert these bigints into dates, you need to divide by a thousand in the DATEADD formula that pootle gave you

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    select
    dateadd(ms, CREATEDTIME-(CREATEDTIME/1000)*1000,
    dateadd(ss, CREATEDTIME/1000+8*60*60, '19700101'))
    from yourtable
    Last edited by pdreyer; 03-27-09 at 04:16.

  5. #5
    Join Date
    Oct 2003
    Location
    now in UAE, i am an INDIAN.
    Posts
    16
    that's fine
    thank you all for the help
    TEAM (Together Everybody Achieve More)

Posting Permissions

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