Thread: last dtae of the month

last dtae of the month

From
Rajesh nalankal
Date:
hi every body
 
 
here is my function to get last day of the month. but it causes error while executing.
 
please help
 
/*
This function returns the last day of the given date's month.
-- Logic --
extract month and year from the given date
build a date of next month by concatnating these values and any day* ( 1- 28)
then substract the given day* from the concatnated date.
*/
declare
dteGivenDate date;
intGivenMonth integer;
intGivenYear integer;
intNextMonth integer;
dteLastDay date;
strTmp varchar;
dteTmp date;
begin
--dteGivenDate:='12-03-2004';
dteGivenDate:=$1;
intGivenYear:=date_part('year',timestamp dteGivenDate);
intGivenMonth:=date_part('month',timestamp dteGivenDate);
intNextMonth:=intGivenMonth + 1;
if intNextMonth >12 then
   intNextMonth:=1;
   intGivenYear:=intGivenYear+1;
end if;
strTmp:= intGivenYear || '-' || intNextMonth || '-' || '10'; -- here we can give any value insted of 10 ranging from (1-28)
dteTmp:= to_date(strTmp,'YYYY MM DD');
dteLastDay:= date dteTmp - 10;
return dteLastDay;
end;
 
 
 
****************
 
is there any other alternative to this function like  a builtin function like last_day ...
 
sincerely rajesh


Do you Yahoo!?
vote.yahoo.com - Register online to vote today!

Re: last dtae of the month

From
Michael Fuhr
Date:
On Sat, Oct 02, 2004 at 11:41:37PM -0700, Rajesh nalankal wrote:
>
> here is my function to get last day of the month. but it causes
> error while executing.
[snip]
> is there any other alternative to this function like  a builtin
> function like last_day

This should get you started:

SELECT date_trunc('month', CURRENT_DATE + INTERVAL'1 month') - INTERVAL'1 day';

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/