--- In firebird-support@yahoogroups.com, Rich Pinder <rpinder@...> wrote:

>
> Is there a built in function (or series of functions) that can be used
> in a SQL statement that converts the default format of the result:
> 2/1/1988
> into a yyyymmdd format, like:
> 19880201
>
> I'm using FB 1.5

Hello Rich,

Formatting is possible but usually it is much easier (and neater) at
the client end.

If you desperately want to do it at the server end, there is the
Extract function that can pull the day, month and year out as
smallints that can form the basis of the query. You need to remember
that you need to left pad with 0 for numbers under 10 in month and
day, so you can hook together with lpad as declared in ib_udf.dll that
ships with Firebird. This function returns strings that are too long,
so you need to cast as varchar(2) to remove trailing spaces. It is
safe to cast instead of using substring because it is impossible to
get 3 digit numbers back. The || operator concatenates two strings.

Before you can use lpad, you will need to declare it in your database

DECLARE EXTERNAL FUNCTION lpad
CSTRING(255), INTEGER, CSTRING(1)
RETURNS CSTRING(255) FREE_IT
ENTRY_POINT 'IB_UDF_lpad' MODULE_NAME 'ib_udf';

Now you can run the following query to format the date in
MYTABLE.MYDATEFIELD.

select cast(Extract(Year from MYDATEFIELD) as varchar(4)) ||
cast(lpad(cast(Extract(Month from MYDATEFIELD) as varchar(2)), 2, '0')
as varchar(2)) || cast(lpad(cast(Extract(Day from MYDATEFIELD) as
varchar(2)), 2, '0') as varchar(2)) AS YYYYMMDD_DATE
FROM MYTABLE


I did warn that it wasn't going to be pretty. If you plan on doing
this sort of thing regularly, you should plan on locating a UDF (or
writing your own) with such a function for the sake of your sanity.

Adam