Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2010
    Posts
    7

    Unanswered: Help with Date Format

    Hi,

    DB2 newbie here.

    Can someone please help me format

    Code:
    SELECT CHAR(current timestamp)
    FROM sysibm.sysdummy1
    from '2010-03-15-11.30.52.607770' to

    '03/15/2010 11:30:52.607770 am/pm?

  2. #2
    Join Date
    Apr 2005
    Location
    USA
    Posts
    130
    You may try some thing like this

    SELECT CHAR(CURRENT_DATE, USA) || ' ' || CHAR(CURRENT_TIME, USA)
    FROM SYSIBM.SYSDUMMY1;

  3. #3
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Something like:
    Code:
    SELECT CHAR(DATE(CURRENT TIMESTAMP)) || ' ' || CHAR(TIME(CURRENT TIMESTAMP))
                                                                                
       FROM SYSIBM.SYSDUMMY1
    You would be best off perusing the SQL Reference manual for how to manipulate dates and times into the format you want.
    Dave

  4. #4
    Join Date
    Mar 2010
    Posts
    7
    Thank you dav1mo & schintala!

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

    Cool The universal solution

    The universal solution:

    Code:
    select char(current date) || ' ' || 
    case 
    when hour(current time) < 12 
    then char(current time) || ' AM' 
    when hour(current time) = 12 
    then char(current time) || ' PM' 
    when hour(current time) = 0 
    then char(current time + 12 hour) || ' AM' 
    when hour(current time) > 12  
    then char(current time - 12 hour) || ' PM' 
    end as "AM/PM Timestamp"
    from sysibm.sysdummy1
    Result:

    AM/PM Timestamp
    2010-03-15 04.25.23 PM
    Lenny

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    from '2010-03-15-11.30.52.607770' to

    '03/15/2010 11:30:52.607770 am/pm?
    The issues of provided solutions are ignoring fractional seconds(or microseconds), like Lenny's Result showed.
    If you dont mind the issue, that's OK.

    To keep fractional seconds, you can use TIMESTAMP_FORMAT(synonym is TO_CHAR) function with rich format-string which is available DB2 9.5 or later for LUW.
    (DB2 Version 9.1 for z/OS and DB2 for i Version 6 Release 1 don't support format strings 'AM', 'PM' and 'HH12'.)

    Here is an example.
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT VARCHAR_FORMAT( current_timestamp , 'mm/dd/yyyy hh12:mi:ss.nnnnnn AM' )
      FROM sysibm.sysdummy1;
    ------------------------------------------------------------------------------
    
    1                              
    --------------------------------------------------
    03/16/2010 06:14:01.214000 AM                     
     
     
      1 record(s) selected.

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    An example without using VARCHAR_FORMAT is...

    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT CHAR( DATE(CURRENT_TIMESTAMP) , USA )
           || ' ' ||
           INSERT( CHAR( TIME(CURRENT_TIMESTAMP) , USA )
                 , 6
                 , 0
                 , ':' || SUBSTR( CHAR(CURRENT_TIMESTAMP) , 18 , 9 )
                 )
      FROM sysibm.sysdummy1;
    ------------------------------------------------------------------------------
    
    1                            
    -----------------------------
    03/16/2010 06:57:30.376000 AM
    
      1 record(s) selected.

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

    Wink

    With small change:

    Code:
    select char(current date, usa) || ' ' 
    || case 
            when hour(current time) < 12 
                   then char(current time) || ' AM' 
            when hour(current time) = 12 
                   then char(current time) || ' PM' 
            when hour(current time) = 0 
                   then char(current time + 12 hour) || ' AM' 
            when hour(current time) > 12  
                   then char(current time - 12 hour) || ' PM' 
       end   as "AM/PM Timestamp"
    from sysibm.sysdummy1
    Result could be:

    03/16/2010 11.07.27 AM
    Lenny

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

    Code:
    when hour(current time) = 0 
                   then char(current time + 12 hour) || ' AM'
    would be never executed.

    Because, there is a following description in the manual "DB2 SQL Reference".
    In general, the value of the case-expression is the value of the result-expression
    following the first (leftmost) when-clause that evaluates to true.
    And, the condition "hour(current time) = 0" is included in the first condition "hour(current time) < 12".

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

    Thumbs up

    Quote Originally Posted by tonkuma View Post
    Lenny,

    Code:
    when hour(current time) = 0 
                   then char(current time + 12 hour) || ' AM'
    would be never executed.

    Because, there is a following description in the manual "DB2 SQL Reference".

    And, the condition "hour(current time) = 0" is included in the first condition "hour(current time) < 12".
    Thank you, tonkuma !

    I could not understood what has to be wrong, and you find !
    Also:
    Code:
    select char(current date, usa) || ' ' 
    || case 
            when hour(current time) = 0 
                   then char(current time + 12 hour) || ' AM' 
            when hour(current time) < 12 
                   then char(current time) || ' AM' 
            when hour(current time) = 12 
                   then char(current time) || ' PM'       
            when hour(current time) > 12  
                   then char(current time - 12 hour) || ' PM' 
       end   as "AM/PM Timestamp"
    from sysibm.sysdummy1
    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
  •