Converting DATE to CHAR

Bambuk · #1 (**permalink**) 11-19-09, 21:00

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.

DB2Plus · #2 (**permalink**) 11-19-09, 22:19

Code:

select replace(varchar(col1), '/', '') from your_table

Kara

Marcus_A · #3 (**permalink**) 11-20-09, 00:47

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).

tonkuma · #4 (**permalink**) 11-20-09, 03:34

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.

tonkuma · #5 (**permalink**) 11-20-09, 03:57

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

Internet Services

Web Development

Digital Marketing

Consumer Tech