Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2011
    Posts
    3

    Unanswered: Db2 - convert timestamp to Date and time

    Hi All,

    I am having problems trying to convert a timestamp in milleseconds to date and time.

    This is what I have been trying but it doen't like the format :-

    WHEN JST_JOB_STREAMS.JST_START_OFFSET <> -1 THEN to_char (to_date(mod(round(JST_JOB_STREAMS.JST_START_OFFSE T/1000),86400), 'SSSSS' ), 'HH24:MIS')

    Thanks in advance

    Steve

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Rather than showing an attempted solution that does not work, it might make more sense to tell us what you have as the input and what you want as the output...

  3. #3
    Join Date
    Jun 2011
    Posts
    3
    Good point

    I have a timestamp field that is displayed in milliseconds that I need converting to human readable format to HH24:MIS

    This is an example of what is stored in the field :79200000

    Thanks

    Steve

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by AcC8braman View Post
    a timestamp field that is displayed in milliseconds
    Milliseconds since what? Epoch? Midnight? Jan. 1st?

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    ...HH24:MIS
    Then it must be time data type, not the timestamp data type.

    If convert miliseconds to time,
    try
    TIME('00:00:00') + (79200000 / 1000) SECONDS

  6. #6
    Join Date
    Jun 2011
    Posts
    3
    Excellent, that worked a treat

    I think the first one was used for an Oracle DB so that's why it wasn't working.

    Thanks for your help peeps

Posting Permissions

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