Skip site navigation (1) Skip section navigation (2)

Re: Last day of month

From: Michael Chaney <mdchaney(at)michaelchaney(dot)com>
To: pgsql-sql(at)postgresql(dot)org, sgnerd(at)yahoo(dot)com(dot)sg
Subject: Re: Last day of month
Date: 2004-02-26 16:37:02
Message-ID: 20040226163702.GC22092@michaelchaney.com (view raw or flat)
Thread:
Lists: pgsql-sql
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(at)michaelchaney(dot)com
http://www.michaelchaney.com/

In response to

pgsql-sql by date

Next:From: KumarDate: 2004-02-27 04:51:31
Subject: Re: Return more than a record
Previous:From: Leo LeoDate: 2004-02-26 15:58:28
Subject: a few Questions about quoted varaibles in psql

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group