Date format 1

[PatelRam]

May be simple question....

I have date in one column(character field) in the following format in a table

Column2
=======

20030123
20030124
20030125


I need to display in following format.

2003/01/23
2003/01/24
2003/01/25

Do you know how ?

 

[PruSQLer]

Yes I do:


select substr(date,1,4) || '/' || substr(date,5,2) || '/' || substr(date,7,2)

from test.date_table

 

[sathyarams]

If this is extensively used in your environemnt, you can create a UDF

Sathyaram

For db2 resoruces visit

http://www.db2click.com

More DB2 questions answered at

http://www.dbforums.com/f8

 

[mctesta]

use the Char function to convert the internal date to an external format.

If you want the USA date format(mm/dd/yyyy):

select char(hiredate,usa)

 

[PruSQLer]

MC

I tried doing your code and got the following error:


14:04:26.141 DBMS GP_TEST -- Error: [IBM][CLI Driver][DB2/NT] SQL0171N The data type, length or value of argument "1" of routine "SYSIBM.CHAR" is incorrect. SQLSTATE=42815

You sure about this?

 

[MarcLodge]

MC's solution will not work as the column is a Char column. I thought that there might be a solution using a combination of DATE and CHAR, but after further consideration, I'm not sure there is. I think the best solution is PruSQL's concatenation.