Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2011
    Posts
    2

    Unanswered: how to convert string to time

    EX : 123456 to HH:MM:SS

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    TO_DATE function
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    main999,

    TO_DATE fuction returns timestamp value, apart from it's name.
    If some date elements were not included in source string, values of CURRENT DATE would be substituted.
    TIME function can be used to extract time value from the result of TO_DATE.

    As aresult,
    TIME( TO_DATE(source_string , 'hh24miss') ) would be a simlest way, now in DB2 SQL.

    Example:
    Code:
    ------------------------------ Commands Entered ------------------------------
    VALUES TIME( TO_DATE('123456' , 'hh24miss') );
    ------------------------------------------------------------------------------
    
    1       
    --------
    12:34:56
    
      1 record(s) selected.

    p.s.
    I tried various ways/expressions to convert from string to time/date/timestamp or reverse.
    It was fun for me.
    Though (I think) we should use DB2 function(s) to convert from string to time/date/timestamp(TO_DATE) and vice versa(TO_CHAR),
    except some specific format which were not supported by those DB2 functions, or for which other simple functions/expression could be applied.
    Last edited by tonkuma; 05-26-12 at 13:36. Reason: Revise English.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Usually using TO_DATE would be a easiest/shortest way to conver from string to time/date/timestamp.

    Even this simple expression(nesting of two functions. at that point, it's same as using TO_DATE)
    is longer than the expression using TO_DATE, even if removed outer TIME function.

    Code:
    ------------------------------ Commands Entered ------------------------------
    VALUES TIME( TRANSLATE('ab:cd:ef' , '123456' , 'abcdef') );
    ------------------------------------------------------------------------------
    
    1       
    --------
    12:34:56
    
      1 record(s) selected.

    Compare the length of expressions which returned same format(datatypes are diffrent).
    Code:
    ------------------------------ Commands Entered ------------------------------
    VALUES ( TRANSLATE('ab:cd:ef' , '123456' , 'abcdef')
           , TIME( TO_DATE('123456' , 'hh24miss') )
           )
    ;
    ------------------------------------------------------------------------------
    
    1        2       
    -------- --------
    12:34:56 12:34:56
    
      1 record(s) selected.
    Last edited by tonkuma; 05-26-12 at 14:21. Reason: Add ", even if removed outer TIME function".

Posting Permissions

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