[RESOLVED] Select substring using php and mysql

Hi Guys hope you can help

I have a database with two fields a date field and an amount field:

date amount
2007-09-27 , 20
2007-10-12 , 22
2007-10-24 , 23
2008-01-15 , 24

Code below, this doesn't work so how would it to work?

"SELECT * FROM payment WHERE substring(date,5,2) LIKE '10'";

I would then want to get the total for that month, which would be 45, which I would use sum(amount) as totalamount.

Hope you can help!

Try:

"SELECT * FROM payment WHERE payment.date = substring(date,5,2) LIKE '10'";

Ahhh i've sorted it!

I have to use substring(payment_date,6,2) NOT substring(payment_date,5,2)

Thanks for looking anyway!

MitchEvans wrote:
Ahhh i've sorted it!

I have to use substring(payment_date,6,2) NOT substring(payment_date,5,2)

Thanks for looking anyway!

Nice to hear it that is worked out, and good that you marked the thread as resolved

Note it would make more sense (and make it clear what you mean) to do this:

SELECT col1, col2 FROM payment WHERE MONTH(`date`) = 10