Re: AW: UDF returning a Boolean

On 2/5/2012 12:36 AM, Birgitta Hauser wrote:

Limited is being kind. They stink. I don't know if you've ever tried

VARCHAR_FORMAT but I've only ever gotten it to work with two format strings:
"YYYY-MM-DD" and "YYYY-MM-DD HH24:MI:SS". Anything else gets me a QRY2293
error.

Are you on the current PTF Level.
I got VARCHAR_FORMAT to run on 6.1 and 7.1 with almost all kinds of formats
(even IYYY = Year for week ISO and IW = Week ISO).
I just run the following SQL-Statement without any problems:
Values(Varchar_Format(Current_Timestamp, 'DD-MM-YY')),
(Varchar_Format(Current_Timestamp, 'YYYYMMDD')),
(VarChar_Format(Current_Timestamp, 'MM-DD-YYYY HH24:MI')),
(VarChar_Format(Current_Timestamp, 'HH24:MI NNNNNN IW Q YY,MM,DD
DDD'));

And this is the result:
05-02-12
20120205
02-05-2012 07:34
07:34 633183 05 1 12,02,05 036

Thanks, Birgitta! No, I was using my little V5R4 machine. I probably should have tried on something a little more recent, no? :) Thanks for the up to date information! You made me do a little more research and I found that VARCHAR_FORMAT was added in DB2 9.5 a few years ago.

And the range of options is quite impressive. Here's Chuck's favorite:

Values(Varchar_Format(Current_Timestamp, 'DD-Mon-YYYY'))

05-Feb-2012

Very nice! My only nitpick is that this is a vendor-specific extension. This code won't work on MSSQL (or any other database that I can find). But it's better than what we used to have, and I can retire my iDate extensions before they get too embedded into the system.

Joe