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