Is there any way to control the formatting when CASTing to a string?

Here's how to reproduce my situation :-

[1]
create table test
(
 datetime timestamp,
 amt numeric(0,2)
);

[2] insert into test values (timestamp '2009-08-01 10:00:00', 20);

[3] select 'Bob paid ' || cast(amt as varchar(30)) || ' on ' ||
cast(datetime as varchar(20)) from test

The result is as follows :-

Bob paid 20 on 2009-8-1 10:00

Primarily, I would like to format the '20' as '20.00'. If possible, I would
also like to be able to format the date/time but I suspect EDB will format
it based on the current locale?

The following works correctly in PGSQL by forcing the output to 2 decimal
places :-
select 'Bob paid ' || cast(cast(amt as numeric(0,2)) as varchar(30)) || ' on
' || cast(datetime as varchar(20)) from test

However the output remains the same in EDB even with the extra CAST.

Any ideas please?

Hedley


Unless Tim comes up with a magic bullet the only suggestion I can make is to write your own custom function to do the formatting


Roy Lambert [Team Elevate]

Hedley,

here a function I use to format a date into the German order:

FUNCTION "FormatDate" (IN "InDate" DATE)
RETURNS VARCHAR(10) COLLATE ANSI
BEGIN
   RETURN CAST(EXTRACT(DAY FROM InDate) as VARCHAR(2)) + '.' +
          CAST(EXTRACT(MONTH FROM InDate) as VARCHAR(2)) + '.' +
          CAST(EXTRACT(YEAR FROM InDate) as VARCHAR(4));
END

Uli

Hedley,

<< Is there any way to control the formatting when CASTing to a string? >>

This is on the list of enhancements and should be available within a month
or so.  It will be an extension with the keyword FORMAT and will most likely
use the same formatting as the import/export in terms of
dates/times/numbers/booleans.

<< The following works correctly in PGSQL by forcing the output to 2 decimal
places :- >>

Hmm, that's a bit weird.

--
Tim Young
Elevate Software
www.elevatesoft.com