Results 1 to 6 of 6
  1. #1
    Join Date
    May 2013
    Posts
    3

    Talking Unanswered: Date and Time Conversion

    Hi all,

    We have a 3rd party database which has a date column with the following data:-

    63518562987097375

    The field is INT64. Does anyone know how to convert this into a human 'readable' date and time? The date and time and time should display '15/10/2012 14:16:13'

    Thank you

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    From just one value pair I can't be certain, but it appears to be a botched Christian epoch calculation expressed in millionths of a second. There are far too many other possible explanations for me to have very deep faith in this analysis.

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

  3. #3
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    The field [sic:columns are not fields] is INT64. Does anyone know how to convert this into a human 'readable' date and time? The date and time and time should display '15/10/2012 14:16:13' [no, use ISO-8601 '2012-10-15 14:16:13']
    Can you find the source and see if they have a procedure for it?

  4. #4
    Join Date
    May 2013
    Posts
    3
    Thanks for the responses guys. I am unable to find the source, but have some more examples:-

    63529263215689000 = 16/02/2013 09:34:33
    63529226555177000 = 15/02/2013 23:23:31
    63511711520467625 = 27/07/2012 19:36:29

    We would like to ideally convert this in excel, as we can with the number of seconds from unix epoch etc. Initially, we thought this is the number of ticks from a start date, but it is not matching the output date.

    Thank you

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Its measuring millionths of a second, starting with the year zero.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    May 2013
    Posts
    3
    Thank you. I have been calculating the number of microseconds since 0000.

    Any ideas on a query that will return the correct value? I am 1 day out after subtracting the 11 days from 1752.

    But then on others, i am 2 days out. Is there any reason for this?

Posting Permissions

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