Subject | Working with string, number and date formating |
---|---|

Author | fkbash |

Post date | 2005-12-20T21:37:07Z |

Hi all,

I'm creating a exportation data routine in my software and I need that

the SQL statment get the data already formated from my BD.

I must be able to get the data on the format below:

- strings: formated with a specific amount of positions, if the string

is shorter than the size need I must put blank spaces on the end of

string;

- date: formated following a parameter (dd/mm/yyyy, ddmmyyyy, yymmdd,

etc);

- number: formated with a specific amount of positions, if the number

is shorter than the size needed I must put zeros on the begin of the

number. The amount of digits in decimal must be a parameter and I need

the number without decimal separator.

I'll use this to do something as bellow:

select formatnumber( a.integerfield, 6, 0 ) || formatstr( a.strfield,

10 ) || formatdate( a.datefield, 'ddmmyyyy' ) || formatnum(

a.floatfield, 10, 2 ) from a;

In the example I'll get the integerfield with 6 digits (000013), the

strfield with 10 positions ('TEST '), the datefield formated

following the parameter (21122005) and floatfield formated with the

total of 10 positions and 2 decimals without decimal separator

(12.55=0000001255).

Can I do this without an UDF???

I know that I do this in Oracle and MySQL, but I don't imagine how I

can do this in Firebird without using UDF. I can use UDF, I only have

problems that I'll need to compile the UDF for windows and linux

plataforms and I don't have a license of Kylix.

Thanks in advance

FabrÃcio F. Kammer

Conchal/SP - Brazil

I'm creating a exportation data routine in my software and I need that

the SQL statment get the data already formated from my BD.

I must be able to get the data on the format below:

- strings: formated with a specific amount of positions, if the string

is shorter than the size need I must put blank spaces on the end of

string;

- date: formated following a parameter (dd/mm/yyyy, ddmmyyyy, yymmdd,

etc);

- number: formated with a specific amount of positions, if the number

is shorter than the size needed I must put zeros on the begin of the

number. The amount of digits in decimal must be a parameter and I need

the number without decimal separator.

I'll use this to do something as bellow:

select formatnumber( a.integerfield, 6, 0 ) || formatstr( a.strfield,

10 ) || formatdate( a.datefield, 'ddmmyyyy' ) || formatnum(

a.floatfield, 10, 2 ) from a;

In the example I'll get the integerfield with 6 digits (000013), the

strfield with 10 positions ('TEST '), the datefield formated

following the parameter (21122005) and floatfield formated with the

total of 10 positions and 2 decimals without decimal separator

(12.55=0000001255).

Can I do this without an UDF???

I know that I do this in Oracle and MySQL, but I don't imagine how I

can do this in Firebird without using UDF. I can use UDF, I only have

problems that I'll need to compile the UDF for windows and linux

plataforms and I don't have a license of Kylix.

Thanks in advance

FabrÃcio F. Kammer

Conchal/SP - Brazil