What the problem with VARCHAR_FORMAT?!?!?!

[Dmitry Bond.]

Hi.

Please see into the

http://publib.boulder.ibm.com/infoce...bafzmst280.htm

My question is - WHY I CANNOT USE

VARCHAR_FORMAT(callofti, 'YYYY-MM-DD')

or

VARCHAR_FORMAT(callofti, 'HH24:MI')

?

It is always reporting

SQL Result : -171. SQL0171N The data type, length or value of argument
n of routine name is incorrect.

My database version is IBM DB2 v8.2.7

How to make it working?!
I mean - VARCHAR_FORMAT(callofti, 'YYYY-MM-DD').
Regards,
Dmitry,

[Dave Hughes]

Dmitry Bond. wrote:

Hi.

Please see into the

http://publib.boulder.ibm.com/infoce...ndex.jsp?topic
=/com.ibm.etools.iseries.langref2.doc/rbafzmst280.htm

My question is - WHY I CANNOT USE

VARCHAR_FORMAT(callofti, 'YYYY-MM-DD')

or

VARCHAR_FORMAT(callofti, 'HH24:MI')

?

It is always reporting

SQL Result : -171. SQL0171N The data type, length or value of argument
n of routine name is incorrect.

My database version is IBM DB2 v8.2.7

How to make it working?!
I mean - VARCHAR_FORMAT(callofti, 'YYYY-MM-DD').

The second parameter in 8.2.7 is rather hilariously pointless. As you
can see from the documentation [1] the second parameter is literally
only permitted to be 'YYYY-MM-DD HH24:MI:SS' (when it says "valid
format strings are", read that as "the ONLY valid format string is").
The function was extended in DB2 9.5 to accept a far wider variety of
possible format strings as you can see from the updated documentation
[2]. If you don't mind using a UDF for formatting timestamps, have a
look at this earlier thread [3] which contains several pretty
comprehensive definitions.
[1]
http://publib.boulder.ibm.com/infoce...om.ibm.db2.udb
..doc/admin/r0007110.htm

[2]
http://publib.boulder.ibm.com/infoce.../com.ibm.db2.l
uw.sql.ref.doc/doc/r0007110.html

[3]
http://groups.google.com/group/comp....se_thread/thre
ad/555ffb95092ffb07
Cheers,

Dave.

--

[Serge Rielau]

PS: The OP also used DB2 for iSeries docs....

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab