DB2 AS400 convert character to date format

[ggnanaraj]

In AS/400, have a table that has a character field defined. This has to
be converted to date format of YYYY-MM-DD.

The sample character data is as follows: 02/05/2005.

In UDB, you can do a bind of DB2 utility packages to use ISO, USA or
some other format. What is to be done in AS/400 to achieve the same?

TIA.

[Karl Hanson]

ggnanaraj wrote:

In AS/400, have a table that has a character field defined. This has to
be converted to date format of YYYY-MM-DD.

The sample character data is as follows: 02/05/2005.

In UDB, you can do a bind of DB2 utility packages to use ISO, USA or
some other format. What is to be done in AS/400 to achieve the same?

TIA.

With DB2 UDB for iSeries you could try this:

select char(date(my_column), iso)

For example using STRSQL:

select char(date('02/05/2005'),iso) from qsys2/qsqptabl

....+....1....+
CHAR conversion
2005-02-05
******** End of data ********

--
Karl Hanson

[ggnanaraj]

Karl Hanson wrote:

ggnanaraj wrote:

In AS/400, have a table that has a character field defined. This has to
be converted to date format of YYYY-MM-DD.

The sample character data is as follows: 02/05/2005.

In UDB, you can do a bind of DB2 utility packages to use ISO, USA or
some other format. What is to be done in AS/400 to achieve the same?

TIA.

With DB2 UDB for iSeries you could try this:

select char(date(my_column), iso)

For example using STRSQL:

select char(date('02/05/2005'),iso) from qsys2/qsqptabl

....+....1....+
CHAR conversion
2005-02-05
******** End of data ********

--
Karl Hanson

Thank you very much, it is working...
[db2inst1@dnetncs db2inst1]$ db2 "select arcdt from JODA.TRNAR# fetch
first 1 rows only"

ARCDT
----------
03/05/2005

1 record(s) selected.

[db2inst1@dnetncs db2inst1]$ db2 "describe select * from JODA.TRNAR#" |
grep -i arcdt
452 CHARACTER 10 ARCDT
6
[db2inst1@dnetncs db2inst1]$

[db2inst1@netncs db2inst1]$ db2 "select char(date(ardt), ISO) from
JOD.TRNA# fetch first 1 rows only"

00001
----------
2005-03-05

1 record(s) selected.

[db2inst1@netncs db2inst1]$