Message-ID: <002101c3bede$ed1e29e0$0f32a8c0@denisnew>
From: "sqllist" <sqllist@coralindia.com>
To: <pgsql-sql@postgresql.org>
Cc: <jeff@dnuk.com>
References: <t6k6tv8unr1dm7kpeb275ca0t9in2i910h@4ax.com>
Subject: Re: How to specify the beginning of the month in Postgres SQL syntax?
Date: Wed, 10 Dec 2003 11:01:54 +0530
MIME-Version: 1.0
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: 7bit


Hi Jeff,

You can use

WHERE date < to_date( to_char(current_date,'yyyy-MM') || '-01','yyyy-mm-dd')

Thanx

Denis


----- Original Message -----
From: <jeff@dnuk.com>
To: <pgsql-sql@postgresql.org>
Sent: Sunday, December 07, 2003 9:46 PM
Subject: [SQL] How to specify the beginning of the month in Postgres SQL
syntax?


> 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?
>
> Thanks.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
>       joining column's datatypes do not match