From: CoL <col@mportal.hu>
Subject: Re: How to specify the beginning of the month in Postgres SQL syntax?
Date: Sun, 07 Dec 2003 20:38:53 +0100
Organization: Hub.Org Networking Services
Lines: 30
Message-ID: <bqvvdq$1m4r$1@news.hub.org>
References: <t6k6tv8unr1dm7kpeb275ca0t9in2i910h@4ax.com>
Reply-To: col@mportal.hu
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US;
	rv:1.6b) Gecko/20031129
In-Reply-To: <t6k6tv8unr1dm7kpeb275ca0t9in2i910h@4ax.com>
To: pgsql-sql@postgresql.org

hi

jeff@dnuk.com wrote, On 12/7/2003 5:16 PM:

> Hello,
> 
> I need to create a view in Postgres that has a where clause of the
> date < beginning of month.
> 
> i.e.:
> SELECT supplier_number, Sum(amount) AS due 
> FROM purchase_orders 
> WHERE date < '2003-12-1' AND paid = 0 
> GROUP BY supplier_number 
> ORDER BY supplier_number ASC
> 
> 
> As you can see, I've specified the 1st of December this year as the
> where clause. What I want is an SQL statement that automatically
> generates the first of the month. How do I do this?
> 

a simple way:
create function month_begin() returns date as '
SELECT to_date(current_date,'YYYY-MM');
' language sql STABLE;

... where date < month_begin()  ....

C.