Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2008
    Posts
    11

    Unanswered: Converting DATE to CHAR

    There is date column on the DB2 table (DD/MM/YYYY). I need to create a view so that the date is displayed as a CHAR (format DDMMYYYY).
    Tried CHAR_TO and Variable_format functions, but they do not work.
    How should I code the select?

    Thanks.

  2. #2
    Join Date
    Jul 2009
    Posts
    150

    Thumbs up easy

    Code:
    select replace(varchar(col1), '/', '') from your_table
    Kara

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    The DD/MM/YYYY format is dependent on the client you use to access the database. It will not display that way if the client has US territory code, or with a client that uses the ISO format YYYY-MM-DD.

    Regardless of the territory code, all dates are stored internally in DB2 as YYYYMMDD in 4 bytes (two numbers per byte since each byte is capable of storing 2 hexidecimal digits 0-F).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    DATE to CHAR (format DDMMYYYY).

    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT date_
         , REPLACE(CHAR(date_, EUR), '.', '')            AS "DDMMYYYY(replace.char)"
         , TRANSLATE('ghefabcd', HEX(date_), 'abcdefgh') AS "DDMMYYYY(translate.hex)"
         , SUBSTR(TO_CHAR(date_, 'DDMMYYYY'), 1, 8)      AS "DDMMYYYY(to_char)"
      FROM (VALUES current_date) q(date_);
    ------------------------------------------------------------------------------
    
    DATE_      DDMMYYYY(replace.char) DDMMYYYY(translate.hex) DDMMYYYY(to_char)
    ---------- ---------------------- ----------------------- -----------------
    2009-11-20 20112009               20112009                20112009         
    
      1 record(s) selected.
    Last edited by tonkuma; 11-20-09 at 04:51.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    TO_CHAR() returns VARCHAR(254).

    Code:
    ------------------------------ Commands Entered ------------------------------
    DESCRIBE 
    SELECT date_
         , REPLACE(CHAR(date_, EUR), '.', '')            AS "DDMMYYYY(replace.char)"
         , TRANSLATE('ghefabcd', HEX(date_), 'abcdefgh') AS "DDMMYYYY(translate.hex)"
         , SUBSTR(TO_CHAR(date_, 'DDMMYYYY'), 1, 8)      AS "DDMMYYYY(substr.to_char)"
         , TO_CHAR(date_, 'DDMMYYYY')                    AS "DDMMYYYY(to_char)"
      FROM (VALUES current_date) q(date_);
    ------------------------------------------------------------------------------
    
     Column Information
    
     Number of columns: 5
    
     SQL type              Type length  Column name                     Name length
     --------------------  -----------  ------------------------------  -----------
     384   DATE                     10  DATE_                                     5
     448   VARCHAR                  10  DDMMYYYY(replace.char)                   22
     448   VARCHAR                   8  DDMMYYYY(translate.hex)                  23
     452   CHARACTER                 8  DDMMYYYY(substr.to_char)                 24
     448   VARCHAR                 254  DDMMYYYY(to_char)                        17

Posting Permissions

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