Results 1 to 8 of 8

Thread: data format

  1. #1
    Join Date
    Oct 2007
    Posts
    246

    Unanswered: data format

    hi

    table is having a column which has a data type char
    but contains date in it
    eg:

    JT_DT char(10)

    '2002-04-18'

    can i convert it into 04-18-2002 in any ways like can we write
    a stored procedure
    rregds
    Pual

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Can you elaborate on what you want to do? Also include the DB2 version and OS you are using.

    Andy

  3. #3
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    mathew paul, if the value in JT_DT is really a Date, the datatype should be DATE and not CHAR(10). This allows you to use Date functions with less effort and it will take up less storage space (4 bytes compared to 10 bytes). And since DB2 stores Dates (and Time and Timestamps) in a modified pack format, you can display it in any of the standard DB2 formats with:

    CHAR(JT_DT, USA)

    The formats are ISO/JIS yyyy-mm-dd, USA mm/dd/yyyy or EUR dd.mm.yyyy

    However, if this can't be changed or you really want the output in a non-standard DB2 format of dd-mm-yyyy you can use SUBSTR to get what you want:

    SUBSTR(JT_DT,6,2) CONCAT '-' CONCAT SUBSTR(JT_DT,9,2) CONCAT '-' CONCAT SUBSTR(JT_DT,1,4)

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Some othet expressions.

    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT jt_dt
         , SUBSTR(jt_dt, 6, 5) || '-' || SUBSTR(jt_dt, 1, 4) expression_1
         , TRANSLATE('ef-gh-abcd', jt_dt, 'abcd-ef-gh')      expression_2
         , REPLACE(CHAR(DATE(jt_dt), USA), '/', '-')         expression_3
         , RIGHT(jt_dt, 5) || '-' || LEFT(jt_dt, 4)          expression_4
      FROM (VALUES CAST('2002-04-18' AS CHAR(10) ) ) AS s(jt_dt)
    ;
    ------------------------------------------------------------------------------
    
    JT_DT      EXPRESSION_1 EXPRESSION_2 EXPRESSION_3 EXPRESSION_4         
    ---------- ------------ ------------ ------------ ---------------------
    2002-04-18 04-18-2002   04-18-2002   04-18-2002   04-18-2002           
    
      1 record(s) selected.

  5. #5
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    Quote Originally Posted by Mathew_paul View Post
    hi

    table is having a column which has a data type char
    but contains date in it
    eg:

    JT_DT char(10)

    '2002-04-18'

    can i convert it into 04-18-2002 in any ways like can we write
    a stored procedure
    rregds
    Pual
    It's easy:

    Code:
    select replace(char(date('2002-04-18'), USA), '/', '-')
      from sysibm.sysdummy1
    Lenny

  6. #6
    Join Date
    Oct 2007
    Posts
    246
    thks for reply
    i did this way
    select substr(del_dt),9,2)||'-'||substr(del_dt),6,2)||'-'||substr(del_dt),1,4) from tabname
    it worked
    regs
    paul

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    select substr(del_dt),9,2)||'-'||substr(del_dt),6,2)||'-'||substr(del_dt),1,4) from tabname
    So, do you want '18-04-2002' from '2002-04-18'?

    It's different from original post.
    eg:

    JT_DT char(10)

    '2002-04-18'

    can i convert it into 04-18-2002 in any ways.....

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If you want '18-04-2002' from '2002-04-18',
    here are two other ways.

    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT jt_dt
         , TRANSLATE('gh-ef-abcd', jt_dt, 'abcd-ef-gh')      expression_2
         , REPLACE(CHAR(DATE(jt_dt), EUR), '.', '-')         expression_3
      FROM (VALUES CAST('2002-04-18' AS CHAR(10) ) ) AS s(jt_dt)
    ;
    ------------------------------------------------------------------------------
    
    JT_DT      EXPRESSION_2 EXPRESSION_3
    ---------- ------------ ------------
    2002-04-18 18-04-2002   18-04-2002  
    
      1 record(s) selected.

Posting Permissions

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