Selecting Orders Before Some Date

[chili5]

Hi:

What I'm trying to do is select all orders in my database before some date. I thought that I would use the < operator on the date in my WHERE clause but this isn't actually working.

SELECT SUM(amount) AS credit FROM group_trans WHERE group_id =5 AND trans_type = 'Credit' AND date < '2009-08-22'

This query makes sense but for some odd reason it is returning the sum of all the orders even ones that are after 2009-08-22. Any thoughts? The field date is a Date field not a char field.

[Jordan]

I don't know what database you are using but that works in some (I think). I always convert the date to UNIXTIMESTAMP to make comparisons, addition and subtraction easy:

[highlight=sql]SELECT SUM(amount) AS credit FROM group_trans WHERE group_id =5 AND trans_type = 'Credit' AND UNIX_TIMESTAMP(date) < UNIX_TIMESTAMP('2009-08-22')[/highlight]

This works in MySQL. Not sure if other databases have this exact function but they probably have something similar at least.

Source: MySQL :: MySQL 5.1 Reference Manual :: 11.6 Date and Time Functions

[chili5]

Thanks, I got it. D +rep!

[relapse]

What did you do to make it work?