Hi Sergio,

Instead going to an UDF please review this code and keep in mind that you will need to do some timestamp cast's if your returning dates will be used in such comparision.

set term ^;
create or alter procedure first_last (
par_month integer not null,
par_year integer not null)
returns (
out_desde date, -- first day of month
out_hasta date) -- last day of month
as
declare variable loc_date_desde date;
begin
-- returns the first and last day of the given month/year
-- get first day date from month and year
loc_date_desde =
cast(
cast( par_year as char(4) ) || '/' || cast( par_month as char(2) ) || '/01'
as date);
-- first day - allways 1-th of the month/year
out_desde = loc_date_desde ;
-- last day
-- this will push loc_date_desde to next month
loc_date_desde = loc_date_desde - extract(day from loc_date_desde) + 33;
-- and compute the last day of previous month
out_hasta = loc_date_desde - extract(day from loc_date_desde);
end^
set term ;^

To get first/last day involved in a direct select (f.e. to get "documents" whithin a month) you may want to consider joining your "documents" table with a "months" table.

Best regards,
Doru

--- In firebird-support@yahoogroups.com, "Sergio H. Gonzalez" <shg_sistemas@...> wrote:
>
>
> Hello, this is another "the best way of doing..." question!
>
> I need a function which returns the fist and last date of a given month of a
> year. This is what I did and is working properly, but I'd like to know if there
> are better ways of doing it. Im using FB 2.1. Probably there is some UDF ?? By
> the way, is there a complete list of UDFs on internet? Thanks! -sergio
>
>
>
> CREATE PROCEDURE FIRST_LAST (
> par_month integer,
> par_year integer)
> returns (
> out_desde date,
> out_hasta date)
> as
> declare variable loc_str_desde char(10);
> begin
>
> /* first day */
> loc_Str_Desde = CAST( par_Year as Char(4) ) || '/' ||
> CAST( par_Month as Char(2) ) || '/01';
>
>
> out_Desde = CAST( loc_Str_Desde as DATE );
>
> EXECUTE PROCEDURE
> NEXT_MONTH (:par_Month, :par_Year)
> RETURNING_VALUES :par_Month, :par_Year;
>
> /* first day of the next month */
> loc_Str_Desde = CAST( par_Year as Char(4) ) || '/' ||
> CAST( par_Month as Char(2) ) || '/01';
>
> /* less 1 = last day of my month */
> out_Hasta = CAST( loc_Str_Desde as DATE ) - 1;
>
> end
>
> -----------------------------------------------------
>
> CREATE PROCEDURE NEXT_MONTH (
> par_month integer,
> par_year integer)
> returns (
> out_month integer,
> out_year integer)
> as
> begin
>
> par_Month = par_Month + 1;
>
>
> if (par_Month > 12) then
> begin
> par_Month = 1;
> par_Year = par_Year + 1;
> end
>
> out_Month = par_Month;
>
> out_Year = par_Year;
>
> end
>