From sgnerd@yahoo.com.sg Wed Feb  25 12:35:19 2004
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id CA55ED1E14C
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 25 Feb 2004 12:35:19 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 36408-05
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 25 Feb 2004 08:35:19 -0400 (AST)
Received: from smtp017.mail.yahoo.com (smtp017.mail.yahoo.com
	[216.136.174.114])
	by svr1.postgresql.org (Postfix) with SMTP id C03F6D1DA69
	for <pgsql-sql@postgresql.org>; Wed, 25 Feb 2004 08:35:15 -0400 (AST)
Received: from unknown (HELO IVESIA67) (sgnerd@203.196.190.138 with login)
	by smtp017.mail.yahoo.com with SMTP; 25 Feb 2004 12:35:07 -0000
Message-ID: <050b01c3fb9b$de4e1e30$7502a8c0@hdsc.com>
Reply-To: "Kumar" <sgnerd@yahoo.com.sg>
From: "Kumar" <sgnerd@yahoo.com.sg>
To: "psql" <pgsql-sql@postgresql.org>
Subject: Last day of month
Date: Wed, 25 Feb 2004 18:05:33 +0530
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_0506_01C3FBC9.F62BF4E0"
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Spam-Status: No, hits=0.2 tagged_above=0.0 required=5.0 tests=HTML_60_70,
	HTML_MESSAGE
X-Spam-Level: 
X-Archive-Number: 200402/344
X-Sequence-Number: 17034

This is a multi-part message in MIME format.

------=_NextPart_000_0506_01C3FBC9.F62BF4E0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Dear friends,

Postgres 7.3.4

How to find the last sunday/mon..../sat of any given month.

Thanks
Kumar

------=_NextPart_000_0506_01C3FBC9.F62BF4E0
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2800.1276" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY style=3D"COLOR: #0000ff; FONT-FAMILY: Arial" bgColor=3D#ffffff>
<DIV><FONT size=3D2>Dear friends,</FONT></DIV>
<DIV><FONT size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2>Postgres 7.3.4</FONT></DIV>
<DIV><FONT size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2>How to find the last sunday/mon..../sat of any given=20
month.</FONT></DIV>
<DIV><FONT size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2>Thanks</FONT></DIV>
<DIV><FONT size=3D2>Kumar</FONT></DIV>
<P></P></BODY></HTML>

------=_NextPart_000_0506_01C3FBC9.F62BF4E0--




From greg@turnstep.com Thu Feb  26 03:07:52 2004
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id 0DD3AD1BA52
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 26 Feb 2004 03:07:52 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 50719-03
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 25 Feb 2004 23:07:53 -0400 (AST)
Received: from biglumber.com (biglumber.com [207.228.252.42])
	by svr1.postgresql.org (Postfix) with SMTP id DA66AD1B973
	for <pgsql-sql@postgresql.org>; Wed, 25 Feb 2004 23:07:48 -0400 (AST)
Received: (qmail 20446 invoked from network); 26 Feb 2004 03:07:52 -0000
Received: from unknown (HELO localhost) (207.228.252.42)
	by 0 with SMTP; 26 Feb 2004 03:07:52 -0000
From: "Greg Sabino Mullane" <greg@turnstep.com>
To: pgsql-sql@postgresql.org
Cc: sgnerd@yahoo.com.sg
Cc: sgnerd@yahoo.com.sg
Subject: Re: Last day of month
X-PGP-Key: 2529 DF6A B8F7 9407 E944  45B4 BC9B 9067 1496 4AC8
X-Request-PGP: 
 http://www.biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
In-Reply-To: <050b01c3fb9b$de4e1e30$7502a8c0@hdsc.com>
Date: Thu, 26 Feb 2004 03:07:52 -0000
X-Mailer: JoyMail 1.46
Message-ID: <542c7f6d5ebe4be44d6b3778850f38ff@biglumber.com>
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200402/354
X-Sequence-Number: 17044


-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
 
 
> How to find the last sunday/mon..../sat of any given month.
 
There is probably a smoother way to do it, but here is a
quick little function to do what you ask. Feed it a date
and a number, where 0 is Sunday, 1 is Monday, etc.
 
CREATE OR REPLACE FUNCTION lastday(date,int) RETURNS DATE AS '
DECLARE
  match date;
  tomorrow date;
BEGIN
 
SELECT TO_DATE(
 (SELECT EXTRACT(\'year\' FROM $1) || \' \' ||
         EXTRACT(\'month\' FROM $1) || \' 01\'),
  \'YYYY MM DD\') INTO tomorrow;
 
LOOP
  tomorrow := tomorrow + \'24 hours\'::interval;
  IF (1 = EXTRACT(\'day\' FROM tomorrow) ) THEN
    RETURN match;
  END IF;
  IF ($2 = EXTRACT(\'dow\' FROM tomorrow)) THEN
    match := tomorrow;
  END IF;
END LOOP;
END;
' LANGUAGE plpgsql;
 
- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200402252206
                                                                                                                             
-----BEGIN PGP SIGNATURE-----
 
iD8DBQFAPWL1vJuQZxSWSsgRAu0tAKDO7oKbxOmfDpCUYpeDSwCwyALs7QCgvKT3
x+aqhBqzm9F87ESbsMe6HdQ=
=AriI
-----END PGP SIGNATURE-----





From mail@joeconway.com Thu Feb  26 04:28:40 2004
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id CFB44D1B91B
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 26 Feb 2004 04:28:40 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 73986-03
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 26 Feb 2004 00:28:37 -0400 (AST)
Received: from joeconway.com (66-146-172-86.skyriver.net [66.146.172.86])
	by svr1.postgresql.org (Postfix) with ESMTP id 1119CD1B57C
	for <pgsql-sql@postgresql.org>; Thu, 26 Feb 2004 00:28:35 -0400 (AST)
Received: from [192.168.5.3] (account jconway HELO joeconway.com)
	by joeconway.com (CommuniGate Pro SMTP 4.1.8)
	with ESMTP-TLS id 1012434; Wed, 25 Feb 2004 20:24:36 -0800
Message-ID: <403D75CD.1000109@joeconway.com>
Date: Wed, 25 Feb 2004 20:27:57 -0800
From: Joe Conway <mail@joeconway.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US;
	rv:1.6) Gecko/20040113
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: Greg Sabino Mullane <greg@turnstep.com>
Cc: pgsql-sql@postgresql.org, sgnerd@yahoo.com.sg
Subject: Re: Last day of month
References: <542c7f6d5ebe4be44d6b3778850f38ff@biglumber.com>
In-Reply-To: <542c7f6d5ebe4be44d6b3778850f38ff@biglumber.com>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200402/356
X-Sequence-Number: 17046

Greg Sabino Mullane wrote:
>>How to find the last sunday/mon..../sat of any given month.
>  
> There is probably a smoother way to do it, but here is a
> quick little function to do what you ask. Feed it a date
> and a number, where 0 is Sunday, 1 is Monday, etc.

How about this:

regression=# select date_trunc('month', current_date + '1 month'::interval);
      date_trunc
---------------------
  2004-03-01 00:00:00
(1 row)

Joe




From mail@joeconway.com Thu Feb  26 04:31:27 2004
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id 33064D1B57C
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 26 Feb 2004 04:31:27 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 67829-10
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 26 Feb 2004 00:31:25 -0400 (AST)
Received: from joeconway.com (66-146-172-86.skyriver.net [66.146.172.86])
	by svr1.postgresql.org (Postfix) with ESMTP id D4910D1B51F
	for <pgsql-sql@postgresql.org>; Thu, 26 Feb 2004 00:31:24 -0400 (AST)
Received: from [192.168.5.3] (account jconway HELO joeconway.com)
	by joeconway.com (CommuniGate Pro SMTP 4.1.8)
	with ESMTP-TLS id 1012438; Wed, 25 Feb 2004 20:27:25 -0800
Message-ID: <403D7676.6080306@joeconway.com>
Date: Wed, 25 Feb 2004 20:30:46 -0800
From: Joe Conway <mail@joeconway.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US;
	rv:1.6) Gecko/20040113
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: Greg Sabino Mullane <greg@turnstep.com>
Cc: pgsql-sql@postgresql.org, sgnerd@yahoo.com.sg
Subject: Re: Last day of month
References: <542c7f6d5ebe4be44d6b3778850f38ff@biglumber.com>
In-Reply-To: <542c7f6d5ebe4be44d6b3778850f38ff@biglumber.com>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200402/357
X-Sequence-Number: 17047

Greg Sabino Mullane wrote:
>>How to find the last sunday/mon..../sat of any given month.
>  
> There is probably a smoother way to do it, but here is a
> quick little function to do what you ask. Feed it a date
> and a number, where 0 is Sunday, 1 is Monday, etc.

oops...forget my last reply...I was a bit too quick on the draw. Try 
this instead:

regression=# select date_trunc('month', current_date + '1 
month'::interval) - '1 day'::interval;
       ?column?
---------------------
  2004-02-29 00:00:00
(1 row)

Joe




From fbax@sympatico.ca Thu Feb  26 14:46:05 2004
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id 0BCC3D1B9A9
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 26 Feb 2004 14:46:05 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 46198-06
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 26 Feb 2004 10:46:04 -0400 (AST)
Received: from tomts13-srv.bellnexxia.net (tomts13-srv.bellnexxia.net
	[209.226.175.34])
	by svr1.postgresql.org (Postfix) with ESMTP id C1F6CD1B994
	for <pgsql-sql@postgresql.org>; Thu, 26 Feb 2004 10:45:59 -0400 (AST)
Received: from acer.sympatico.ca ([65.93.65.138]) by
 tomts13-srv.bellnexxia.net
	(InterMail vM.5.01.06.05 201-253-122-130-105-20030824) with ESMTP id
	<20040226144558.DTHM25409.tomts13-srv.bellnexxia.net@acer.sympatico.ca>
	for <pgsql-sql@postgresql.org>; Thu, 26 Feb 2004 09:45:58 -0500
Message-Id: <5.2.1.1.0.20040226093422.029e0560@pop6.sympatico.ca>
X-Sender: b1wudu74@pop6.sympatico.ca
X-Mailer: QUALCOMM Windows Eudora Version 5.2.1
Date: Thu, 26 Feb 2004 09:45:05 -0500
To: pgsql-sql@postgresql.org
From: Frank Bax <fbax@sympatico.ca>
Subject: Re: Last day of month
In-Reply-To: <403D7676.6080306@joeconway.com>
References: <542c7f6d5ebe4be44d6b3778850f38ff@biglumber.com>
	<542c7f6d5ebe4be44d6b3778850f38ff@biglumber.com>
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"; format=flowed
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200402/364
X-Sequence-Number: 17054

At 11:30 PM 2/25/04, Joe Conway wrote:

>Greg Sabino Mullane wrote:
>>>How to find the last sunday/mon..../sat of any given month.
>>
>>There is probably a smoother way to do it, but here is a
>>quick little function to do what you ask. Feed it a date
>>and a number, where 0 is Sunday, 1 is Monday, etc.
>
>oops...forget my last reply...I was a bit too quick on the draw. Try this 
>instead:
>
>regression=# select date_trunc('month', current_date + '1 
>month'::interval) - '1 day'::interval;
>       ?column?
>---------------------
>  2004-02-29 00:00:00
>(1 row)
>
>Joe


But the original request was for a specific day-of-week.  So use Joe's 
answer above to get last day of month, and use 'dow' to determine the 
day-of-week of that day.  Let's call that dow1.  If the day-of-week being 
asked for is dow2 then:
         if dow1 < dow2
                 return (last-day-of-month - dow1 - 7 + dow2)
         else
                 return (last-day-of-month - dow1 + dow2)
I'm no good at coding pgsql functions, so I'm not going to attempt proper 
syntax.

Frank 




From mdchaney@michaelchaney.com Thu Feb  26 16:36:19 2004
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id 9DC0AD1CA83
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 26 Feb 2004 16:36:19 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 89096-09
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 26 Feb 2004 12:36:12 -0400 (AST)
Received: from mail.michaelchaney.com (mail.michaelchaney.com [207.65.84.68])
	by svr1.postgresql.org (Postfix) with ESMTP id A59C0D1B91B
	for <pgsql-sql@postgresql.org>; Thu, 26 Feb 2004 12:36:11 -0400 (AST)
Received: from pcp08698233pcs.500ash01.tn.comcast.net (fractal.trailer)
	[69.137.114.17]
	by mail.michaelchaney.com with esmtp (Exim 4.20 #1 (FreeBSD))
	id 1AwOQO-000FCI-3c; Thu, 26 Feb 2004 10:31:48 -0600
Received: from mdchaney by fractal.trailer with local (Exim 3.35 #1 (Debian))
	id 1AwOVS-00061J-00; Thu, 26 Feb 2004 10:37:02 -0600
Date: Thu, 26 Feb 2004 10:37:02 -0600
From: Michael Chaney <mdchaney@michaelchaney.com>
To: pgsql-sql@postgresql.org, sgnerd@yahoo.com.sg
Subject: Re: Last day of month
Message-ID: <20040226163702.GC22092@michaelchaney.com>
References: <050b01c3fb9b$de4e1e30$7502a8c0@hdsc.com>
	<542c7f6d5ebe4be44d6b3778850f38ff@biglumber.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <542c7f6d5ebe4be44d6b3778850f38ff@biglumber.com>
User-Agent: Mutt/1.3.28i
Organization: Michael Chaney Consulting Corporation
X-URL: http://www.michaelchaney.com/
X-MailScanner-Information: Please see
	http://www.michaelchaney.com/mail/spam.php for more information
X-MailScanner: Found to be clean
X-MailScanner-SpamCheck: not spam, SpamAssassin (score=-3.4, required 5,
	IN_REP_TO)
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200402/369
X-Sequence-Number: 17059

On Thu, Feb 26, 2004 at 03:07:52AM -0000, Greg Sabino Mullane wrote:
> 
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>  
>  
> > How to find the last sunday/mon..../sat of any given month.
>  
> There is probably a smoother way to do it, but here is a
> quick little function to do what you ask. Feed it a date
> and a number, where 0 is Sunday, 1 is Monday, etc.

select date_trunc('month', current_date + '1 month'::interval) - '1 day'::interval +
        (((3 - 7 - to_char(date_trunc('month', current_date + '1 month'::interval) -
        '1 day'::interval,'D')::int) %7)||' days')::interval;

The "3" is the day of week (1 = Sunday, 7 = Saturday).  This equation
will return the date of the last "x" of the current month.  Change
"current_date" to be whatever date you wish to find the last "x" of.

Michael
--
Michael Darrin Chaney
mdchaney@michaelchaney.com
http://www.michaelchaney.com/