Results 1 to 11 of 11
  1. #1
    Join Date
    Jul 2009
    Posts
    58

    Unanswered: Need help with time formating

    Hi Guys,

    I am getting the time format (HH:MM AM/PM) from a timestamp field by the following code
    <CODE>
    CHAR(time(th.T_STMP_MODFD),usa)</CODE>

    But i want it in HH: MM: SS AM/PM.

    Thanks for your help in advance.

    Regards,
    Magesh

  2. #2
    Join Date
    Jul 2009
    Posts
    58
    trying with this correct me if its wrong
    <CODE>
    concat(concat(CONCAT( SUBSTRING(CHAR(TIME(TH.T_STMP_MODFD),USA),1,(LENGT H(CHAR(TIME(TH.T_STMP_MODFD),USA))-3),CODEUNITS32),':'),
    SUBSTRING(CHAR(TIME(TH.T_STMP_MODFD)),(LENGTH(CHAR (TIME(TH.T_STMP_MODFD),USA))-1),LENGTH(CHAR(TIME(TH.T_STMP_MODFD),USA)),CODEUNI TS32))
    ,SUBSTRING(CHAR(TIME(TH.T_STMP_MODFD),USA),(LENGTH (CHAR(TIME(TH.T_STMP_MODFD),USA))-2),LENGTH(CHAR(TIME(TH.T_STMP_MODFD),USA)),CODEUNI TS32)
    )
    </CODE>
    Last edited by mac4rfree; 06-10-10 at 05:55. Reason: Got solution

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT th.t_stmp_modfd
         , concat(concat(CONCAT( SUBSTRING(CHAR(TIME(TH.T_STMP_MODFD),USA),1,(LENGTH(CHAR(TIME(TH.T_STMP_MODFD),USA))-3),CODEUNITS32),':'),
           SUBSTRING(CHAR(TIME(TH.T_STMP_MODFD)),(LENGTH(CHAR (TIME(TH.T_STMP_MODFD),USA))-1),LENGTH(CHAR(TIME(TH.T_STMP_MODFD),USA)),CODEUNITS32))
           ,SUBSTRING(CHAR(TIME(TH.T_STMP_MODFD),USA),(LENGTH (CHAR(TIME(TH.T_STMP_MODFD),USA))-2),LENGTH(CHAR(TIME(TH.T_STMP_MODFD),USA)),CODEUNITS32)
           ) AS expr_1 
         , CHAR( INSERT( CHAR(TIME(th.t_stmp_modfd) , USA)
                       , 6
                       , 0
                       , ':' || SUBSTR(CHAR(th.t_stmp_modfd) , 18 , 2)
                       )
               , 11
               ) AS expr_2
         , LEFT(CHAR(TIME(th.t_stmp_modfd) , USA) , 5)
           || SUBSTR(CHAR(TIME(th.t_stmp_modfd) , JIS) , 6 , 3)
           || SUBSTR(CHAR(TIME(th.t_stmp_modfd) , USA) , 6 , 3)
           AS expr_3
      FROM (VALUES CURRENT_TIMESTAMP
                 , CURRENT_TIMESTAMP - 12 HOURS
           ) AS th(t_stmp_modfd)
    ;
    ------------------------------------------------------------------------------
    
    T_STMP_MODFD               EXPR_1                    EXPR_2      EXPR_3        
    -------------------------- ------------------------- ----------- --------------
    2010-06-10-18.17.12.318000 06:17:12 PM               06:17:12 PM 06:17:12 PM   
    2010-06-10-06.17.12.318000 06:17:12 AM               06:17:12 AM 06:17:12 AM   
    
      2 record(s) selected.
    It looks that your expression(expr_1) worked well.
    But, there will be shorter expressions(e.g. expr_2, expr_3).

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    You can use "["CODE"]" and "["/CODE"]"(remove double quotations) for Code.

  5. #5
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Lightbulb Shorter

    Is this one not good:

    Code:
    select right(digits(hour(current timestamp)),   2) ||':' ||
           right(digits(minute(current timestamp)), 2) ||':' || 
           right(digits(second(current timestamp)), 2) ||' ' ||   
           case when hour(current timestamp)between 0 and 11 then 'AM'
                else 'PM' end 
    from sysibm.sysdummy1
    Result:

    10:13:28 AM
    Lenny

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Is this one not good:
    It would be not good from TIME('13:00:00') to TIME('23:59:59').
    Because, hours part will show 13 to 23.

  7. #7
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Exclamation Correction

    Quote Originally Posted by tonkuma View Post
    It would be not good from TIME('13:00:00') to TIME('23:59:59').
    Because, hours part will show 13 to 23.
    Thank you, tonkuma !

    Trying to fix:

    Code:
    select right(digits(
    case when hour(current timestamp)  = 0 
         then 12 
         when hour(current timestamp) < 12 
         then hour(current timestamp)
         else hour(current timestamp) - 12
    end ), 2) ||':' ||
           right(digits(minute(current timestamp)), 2) ||':' || 
           right(digits(second(current timestamp)), 2) ||' ' ||   
           case when hour(current timestamp)between 0 and 11 then 'AM'
                else 'PM' 
           end  "EST Time"
    from sysibm.sysdummy1
    Result:

    EST Time
    03:29:13 PM
    Lenny

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    It'll depend on OP's requirement.


    According to my brief test,
    hours part in the result of CHAR(time-expression , USA) function showed as following table:
    (I don't know wheather these results are common sense in USA.)
    Code:
    time-expression       | CHAR(time-expression , USA)
    ----------------------+----------------------------
    00:00:00 <-> 00:00:59 | 00:00 AM
    00:01:00 <-> 00:59:59 | 12:01 AM <-> 12:59 AM
    01:00:00 <-> 11:59:59 | 01:00 AM <-> 11:59 AM
    12:00:00 <-> 12:59:59 | 12:00 PM <-> 12:59 PM
    13:00:00 <-> 23:59:59 | 01:00 PM <-> 11:59 PM
    Last edited by tonkuma; 06-10-10 at 18:00. Reason: Change "Still not good." to "It'll depend on OP's requirement."

  9. #9
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    00:00:00 <-> 00:00:59 | 12:00 AM <-> 12:59 AM

    Lenny

  10. #10
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    00:00:00 <-> 00:00:59 | 12:00 AM <-> 12:59 AM
    No. Here is a test result.
    (Tested on DB2 Express-C 9.7.2 on Windows.)
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT time_expr
         , CHAR(time_expr , USA) time_usa
         , CHAR(time_expr , ISO) time_iso
      FROM (SELECT TIME(time_char) time_expr
              FROM (VALUES '00:00:00'
                         , '00:00:59'
                         , '00:01:00'
                         , '00:59:59'
                         , '01:00:00'
                         , '11:59:59'
                         , '12:00:00'
                         , '12:59:59'
                         , '13:00:00'
                         , '23:59:59'
                   ) t(time_char)
           )
    ;
    ------------------------------------------------------------------------------
    
    TIME_EXPR TIME_USA TIME_ISO
    --------- -------- --------
    00:00:00  00:00 AM 00.00.00
    00:00:59  00:00 AM 00.00.59
    00:01:00  12:01 AM 00.01.00
    00:59:59  12:59 AM 00.59.59
    01:00:00  01:00 AM 01.00.00
    11:59:59  11:59 AM 11.59.59
    12:00:00  12:00 PM 12.00.00
    12:59:59  12:59 PM 12.59.59
    13:00:00  01:00 PM 13.00.00
    23:59:59  11:59 PM 23.59.59
    
      10 record(s) selected.

  11. #11
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    Something wrong: In USA 00:mm:ss doesn't exist.

    Lenny

Posting Permissions

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