The CAST function is very useful in EDB

CAST(<Expression> AS <DataType>
[DATE FORMAT <DateFormat>]
[TIME FORMAT <TimeFormat> [AM LITERAL <AMLiteral> PM LITERAL <PMLiteral>]]
[DECIMAL CHAR <DecimalChar>]
[BOOLEAN TRUE LITERAL <TrueLiteral> FALSE LITERAL <FalseLiteral>]
)

It allows data to be returned as string / character data in a readable format for the user without the need for further complex coding by the programmer.

However if I use the expression:

CAST(<SomeFloatField> as VARCHAR)

or

CAST(<SomeIntegerField> AS VARCHAR)

The data might return with many decimal places and without decimal formatting.

i.e. a field value 3,210,123.40 will display as 3210123.4

It would be great to add FLOAT FORMAT <> and INTEGER FORMAT <> extensions to the CAST FUNCITON.

These could take literal strings in the form:

'#,###.00'

as per programming norms, allowing users to return well formatted values more easily. i.e.

CAST(<SomeFloatField> as VARCHAR FLOAT FORMAT '#,###.00')

Adam


I'd suggest leaving it as is and just getting more creative eg

CAST(CAST(<SomeFloatField> AS DECIMAL(19,3)) as VARCHAR)+'.00'

that is unless you want to extend your suggestion to include all the standard formatting variations (which I always have to look up - just can't remember them)

Another alternative would be to write a small external function in Delphi, if you want left & right padding I seem to recall someone posting routines into the extensions ng.

Roy Lambert

Adam,

<< It would be great to add FLOAT FORMAT <> and INTEGER FORMAT <> extensions to the CAST FUNCITON. >>

Noted, thanks.

Tim Young
Elevate Software
www.elevatesoft.com

I agree that there are lots of ways of nesting CAST functions which get around the issue, but it is a fairly minor extension to the function, which already does really useful work on DATE and TIMESTAMPs ... so I figure it gives the CAST a bit more completeness, and makes the SQL neater.

Tim does a good enough job with the Help Files that there ought to be a list of format options on hand when you need to use it too!