data format

Mathew_paul · #1 (**permalink**) 03-19-10, 03:26

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

ARWinner · #2 (**permalink**) 03-19-10, 08:39

Can you elaborate on what you want to do? Also include the DB2 version and OS you are using.

Andy

Stealth_DBA · #3 (**permalink**) 03-19-10, 09:59

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)

tonkuma · #4 (**permalink**) 03-19-10, 11:11

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.

Lenny77 · #5 (**permalink**) 03-19-10, 11:24

Quote:

Originally Posted by Mathew_paul

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

Mathew_paul · #6 (**permalink**) 03-20-10, 03:57

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

tonkuma · #7 (**permalink**) 03-20-10, 04:16

Quote:

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.

Quote:

eg:

JT_DT char(10)

'2002-04-18'

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

tonkuma · #8 (**permalink**) 03-20-10, 05:58

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech