Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2013
    Posts
    2

    Unanswered: Date in YYYY-MM-DDTHH:MM:mmm

    hi, may i know how to format the date into above format in db2?

    In oracle it will be
    to_date('2013-03-06T16:12:39', 'YYYY-MM-DD"T"HH24:MIS')

    thanks

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    date is always YYYY-MM-DD
    timestamp is YY-MM-DD-HH.MM.SS.mmmmmm
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Oct 2012
    Posts
    5
    For changing time into string with format, db2 is not flexible as Oracle. It only has limited types, such as:
    char(current date,ISO)——changing into: yyyy-mm-dd
    char(current date,USA)——changing into: mm/dd/yyyy
    char(current date,EUR)——changing into: dd.mm.yyyy

    If you want more flexible formats, you should SUBSTR to divide the string, the following is a common used UDF:
    create function ts_fmt(TS timestamp, fmt varchar(20))
    returns varchar(50)
    return
    with tmp (dd,mm,yyyy,hh,mi,ss,nnnnnn) as
    (
    select
    substr( digits (day(TS)),9),
    substr( digits (month(TS)),9) ,
    rtrim(char(year(TS))) ,
    substr( digits (hour(TS)),9),
    substr( digits (minute(TS)),9),
    substr( digits (second(TS)),9),
    rtrim(char(microsecond(TS)))
    from sysibm.sysdummy1
    )
    select
    case fmt
    when 'yyyymmdd'
    then yyyy || mm || dd
    when 'mm/dd/yyyy'
    then mm || '/' || dd || '/' || yyyy
    when 'yyyy/dd/mm hh:mi:ss'
    then yyyy || '/' || mm || '/' || dd || ' ' ||
    hh || ':' || mi || ':' || ss
    when 'nnnnnn'
    then nnnnnn
    else
    'date format ' || coalesce(fmt,' ') ||
    ' not recognized.'
    end
    from tmp

  4. #4
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    waterduck, I don't know if you can get the exact format you are looking for, but check out TIMESTAMP_FORMAT - IBM DB2 9.7 for Linux, UNIX, and Windows.

    This is for DB2 LUW V9.7. You don't mention which DB2 you are using.

  5. #5
    Join Date
    Mar 2013
    Posts
    2
    im using this;may look ugly
    TO_DATE(REPLACE('2013-01-01T00:00:00', 'T', ' '), 'YYYY-MM-DD HH24:MIS')

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by waterduck View Post
    hi, may i know how to format the date into above format in db2?

    In oracle it will be
    to_date('2013-03-06T16:12:39', 'YYYY-MM-DD"T"HH24:MIS')

    thanks
    From your example, you did string('2013-03-06T16:12:39') to date in Oracle.
    But, you want "how to format the date into above format "(of string, may be...)

    Which of "string to date" or "date into string(of above format)" do you want?

    Anyway,
    here are some examples.

    Example 1: string('2013-03-06T16:12:39') to timestamp or date.
    Code:
    ------------------------------ Commands Entered ------------------------------
    VALUES
      (  TO_DATE(REPLACE('2013-03-06T16:12:39' , 'T' , ' ') , 'yyyy-mm-dd hh24:mi:ss')
         /* waterduck's solution */
       , TIMESTAMP( REPLACE('2013-03-06T16:12:39' , 'T' , ' ') )
       , DATE( LEFT('2013-03-06T16:12:39' , 10) )
      )
    ;
    ------------------------------------------------------------------------------
    
    1                          2                          3         
    -------------------------- -------------------------- ----------
    2013-03-06-16.12.39.000000 2013-03-06-16.12.39.000000 2013-03-06
    
      1 record(s) selected.
    Example 2: Timestamp to string.
    Code:
    ------------------------------ Commands Entered ------------------------------
    VALUES
      (  LEFT( INSERT( REPLACE( current timestamp , '.' , ':') , 11 , 1 , 'T' ) , 19 ) 
       , TRANSLATE('abcd-ef-ghTij:kl:mn' , current timestamp , 'abcd-ef-gh-ij.kl.mn')
       , REPLACE( TO_CHAR(current timestamp , 'yyyy-mm-dd hh24:mi:ss') , ' ' , 'T' )
      )
    ;
    ------------------------------------------------------------------------------
    
    1                           2                   3                                                                                                                                                                                                                                                             
    --------------------------- ------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    2013-03-15T05:39:01         2013-03-15T05:39:01 2013-03-15T05:39:01                                                                                                                                                                                                                                           
    
      1 record(s) selected.
    Last edited by tonkuma; 03-15-13 at 04:25. Reason: Add another expression(second) to Example 1. Add a comment /* waterduck's solution */ in Example 1.

Posting Permissions

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