Thread: Retrieve month from date in SQl query

I am using PGSQL 8.2.3 on Windows XP.

I have a table called "StudentFeesPayment" with columns "ReceiptNo" and
"ReceiptMonthYear".
The column, "ReceiptMonthYear" stores date in the format "yyyy-mm-dd". I
have to find the max(ReceiptNo) where Month of (ReceiptMonthYear)=4. Or
whatever month I give.

I tried:

Select max(ReceiptNo) from StudentFeesPayment where
Extract(Month('ReceiptMonthYear'))=4;

But it is not working. How to do it?
-- 
View this message in context: http://www.nabble.com/Retrieve-month-from-date-in-SQl-query-tf3602302.html#a10063035
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.

On Wed, Apr 18, 2007 at 10:36:14AM -0700, RPK wrote:
> I have a table called "StudentFeesPayment" with columns "ReceiptNo" and
> "ReceiptMonthYear".
> The column, "ReceiptMonthYear" stores date in the format "yyyy-mm-dd". I
> have to find the max(ReceiptNo) where Month of (ReceiptMonthYear)=4. Or
> whatever month I give.
> 
> I tried:
> 
> Select max(ReceiptNo) from StudentFeesPayment where
> Extract(Month('ReceiptMonthYear'))=4;
> 
> But it is not working. How to do it?

Please explain how it's not working -- how does the result differ
from your expectation?  I'd guess you're getting a syntax error;
see the documentation for the correct way to use extract():

http://www.postgresql.org/docs/8.2/interactive/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT

-- 
Michael Fuhr