Thread: function to find last day of month

*****************************************************************************************

DISCLAIMER:

This email is from the Scottish Crop Research Institute, but the views expressed by the sender are not necessarily the views of SCRI and its subsidiaries.  This email and any files transmitted with it are confidential to the intended recipient at the e-mail address to which it has been addressed.  It may not be disclosed or used by any other than that addressee.
If you are not the intended recipient you are requested to preserve this confidentiality and you must not use, disclose, copy, print or rely on this e-mail in any way. Please notify mail@scri.sari.ac.uk quoting the name of the sender and delete the email from your system.

Although SCRI has taken reasonable precautions to ensure no viruses are present in this email, neither the Institute nor the sender accepts any responsibility for any viruses, and it is your responsibility to scan the email and the attachments (if any).

Hello

try:

CREATE OR REPLACE FUNCTION LastDateOfMonth(date) RETURNS date AS '
  SELECT CAST(date_trunc(''month'', $1) + interval ''1 month''
    - interval ''1 day'' as date);
' LANGUAGE sql;

Regards
Pavel

On Fri, 5 Dec 2003, Jennifer Lee wrote:

> Hello,
>
> Does anyone happen to have a function, prefereably in pl/pgsql, which
> given the month and year will return the last day in that month?
>
> Thanks,
> Jennifer
>
>
> *****************************************************************
> DISCLAIMER:
>
> This email is from the Scottish Crop Research Institute, but the views expressed by the sender are not necessarily
theviews of SCRI and its subsidiaries.  This email and any files transmitted with it are confidential to the intended
recipientat the e-mail address to which it has been addressed.  It may not be disclosed or used by any other than that
addressee.
> If you are not the intended recipient you are requested to preserve this confidentiality and you must not use,
disclose,copy, print or rely on this e-mail in any way. Please notify mail@scri.sari.ac.uk quoting the name of the
senderand delete the email from your system. 
>
> Although SCRI has taken reasonable precautions to ensure no viruses are present in this email, neither the Institute
northe sender accepts any responsibility for any viruses, and it is your responsibility to scan the email and the
attachments(if any). 
>
>
>

> Hello,
>
> Does anyone happen to have a function, prefereably in pl/pgsql, which
> given the month and year will return the last day in that month?
>

Something like this might get you started:

CREATE OR REPLACE FUNCTION public.fom(date)
  RETURNS date AS
'
SELECT COALESCE($1, CURRENT_DATE)-EXTRACT(DAY FROM COALESCE($1,
CURRENT_DATE))::INTEGER+1;
'
  LANGUAGE 'sql' VOLATILE;



CREATE OR REPLACE FUNCTION public.lom(date)
  RETURNS date AS
'
SELECT fom(date (COALESCE($1, CURRENT_DATE) + interval \'1 month\'))-1;
'
  LANGUAGE 'sql' VOLATILE;


~Berend Tober

Thanks, and to everyone else who sent suggestions they were all
fantastic. I've got a working function now.

Cheers,
Jennifer

-----Original Message-----
From: Pavel Stehule [mailto:stehule@kix.fsv.cvut.cz]
Sent: Friday, December 05, 2003 1:05 PM
To: Jennifer Lee
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] function to find last day of month

Hello

try:

CREATE OR REPLACE FUNCTION LastDateOfMonth(date) RETURNS date AS '
  SELECT CAST(date_trunc(''month'', $1) + interval ''1 month''
    - interval ''1 day'' as date);
' LANGUAGE sql;

Regards
Pavel

On Fri, 5 Dec 2003, Jennifer Lee wrote:

> Hello,
>
> Does anyone happen to have a function, prefereably in pl/pgsql, which
> given the month and year will return the last day in that month?
>
> Thanks,
> Jennifer
>
>
> *****************************************************************
> DISCLAIMER:
>
> This email is from the Scottish Crop Research Institute, but the views
expressed by the sender are not necessarily the views of SCRI and its
subsidiaries.  This email and any files transmitted with it are
confidential to the intended recipient at the e-mail address to which it
has been addressed.  It may not be disclosed or used by any other than
that addressee.
> If you are not the intended recipient you are requested to preserve
this confidentiality and you must not use, disclose, copy, print or rely
on this e-mail in any way. Please notify mail@scri.sari.ac.uk quoting
the name of the sender and delete the email from your system.
>
> Although SCRI has taken reasonable precautions to ensure no viruses
are present in this email, neither the Institute nor the sender accepts
any responsibility for any viruses, and it is your responsibility to
scan the email and the attachments (if any).

>
>
>


*****************************************************************
DISCLAIMER:

This email is from the Scottish Crop Research Institute, but the views expressed by the sender are not necessarily the
viewsof SCRI and its subsidiaries.  This email and any files transmitted with it are confidential to the intended
recipientat the e-mail address to which it has been addressed.  It may not be disclosed or used by any other than that
addressee.
If you are not the intended recipient you are requested to preserve this confidentiality and you must not use,
disclose,copy, print or rely on this e-mail in any way. Please notify mail@scri.sari.ac.uk quoting the name of the
senderand delete the email from your system. 

Although SCRI has taken reasonable precautions to ensure no viruses are present in this email, neither the Institute
northe sender accepts any responsibility for any viruses, and it is your responsibility to scan the email and the
attachments(if any).