Results 1 to 5 of 5

Thread: Date conversion

  1. #1
    Join Date
    May 2009
    Posts
    11

    Unanswered: Date conversion

    I have a column which gives date as Java long type - 1241436617660,1241436617145 etc. How do I convert this to

    Required Result as -
    Mon May 04 07:30:18 EDT 2009

    select td from table_time;

    Input
    td - 1241436617660,1241436617145

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I assume the value is in milliseconds based on you other post.

    select timestamp('01/01/1970','00:00:00') + (Mycol * 1000) microseconds from ...


    Andy

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I thoght that you need to do conversions of these three steps.
    1) Miliseconds to DB2 TIMESTAMP.
    2) UTC to EDT(local time zone).
    3) Format it as 'DAY MON DD HH24:MI:SS EDT YYYY'.

    Andy answered for step 1).

    For step 2), add CURRENT TIMEZONE to UTC.
    Or, you may want to use NEW_TIME in Sample UDFs for Migration.

    For step 3), you may want to use VARCHAR_FORMAT(a synonym is TO_CHAR) for DD, HH24, MI. SS and YYYY
    on DB2 for LUW 9.5, DB2 for iSeries V6R1 or DB2 for z/OS V9.
    You can use SUBSTR(DAYNAME(ts), 1, 3) for DAY and SUBSTR(MONTHNAME(ts), 1, 3) for MON.

  4. #4
    Join Date
    May 2009
    Posts
    11
    I'm using this, but time seems to invalid....

    DATE(
    TIMESTAMP('1970-01-01','00.00.00')
    + (CAST(t.timestampis as DECIMAL(31,0))/1000) seconds ) ,

    TIME(
    TIMESTAMP('1970-01-01-00.00.00')
    + (CAST(t.timestampis as DECIMAL(31,0))/1000) seconds

    Please advise.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Conversion from Timestamp to Decimal will return yyyymmddhhmmss.nnnnnn.

    So, CAST(t.timestampis as DECIMAL(31,0))/1000 may return yyyymmddhhm.

Posting Permissions

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