Your method for obtaining the last date of a month is not quite correct, Marcelo. Suppose the date in question was 30 January 2009. Adding 31 to this date gives you a date in March, and the day before 1 March this year was 28 February, whereas your result ought to have been 31 January. Now, it should be simple to fix, though the fix requires a case construct for avoiding the month '13':

/* obtaining last date of a month of a especific date */
cast(case when extract(month from cast(:questdate as timestamp)) < 12 then extract(month from cast(:questdate as timestamp))+1 else '01' end||'/01/'||extract(year from cast(:questdate as timestamp)+31) as timestamp) - 1 lastdate

(since December always has 31 days, I think it should be safe to add 31 to the date when looking for the year.)

Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of mgaldinonet
Sent: 16. april 2009 23:19
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Re: first and last day of a month


With 2 SQL instructions you can obtain the dates

/* obtaining first date of a month of a especific date */
cast(extract(month from cast(:questdate as timestamp))||'/01/'||extract(year from cast(:questdate as timestamp)) as timestamp) firstdate

/* obtaining last date of a month of a especific date */
cast(extract(month from cast(:questdate as timestamp)+31)||'/01/'||extract(year from cast(:questdate as timestamp)+31) as timestamp) - 1 lastdate


Best regards.

Marcelo.

--- 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
>




------------------------------------

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Yahoo! Groups Links