Need to get all orders in a date range by sql

[navido]

Hi

From phpadmin i try to get all orders (only some columns) from the table orders.

I need to get a list for march 2013

This is what i have tried (this is just an example i tried for 2011):

SELECT 'orders_id',`customers_name`,`order_total`,`payment_method` FROM 'orders' WHERE 'date_purchased' BETWEEN '2011-02-01' and '2011-02-01';

But i get en error.

Can anyone show me the code i have to use?

Thanks

[g2ktcf]

Hi

From phpadmin i try to get all orders (only some columns) from the table orders.

I need to get a list for march 2013

This is what i have tried (this is just an example i tried for 2011):

SELECT 'orders_id',`customers_name`,`order_total`,`payment_method` FROM 'orders' WHERE 'date_purchased' BETWEEN '2011-02-01' and '2011-02-01';

But i get en error.

Can anyone show me the code i have to use?

Thanks

Well it would be helpful to know what the error is......

[g2ktcf]

your dates not dates but text from the why I see it....look at this example.

SELECT *
FROM orders
WHERE order_date >= to_date('2003/01/01', 'yyyy/mm/dd')
AND order_date <= to_date('2003/12/31','yyyy/mm/dd');

[jc8125]

try this:

SELECT 'orders_id',`customers_name`,`order_total`,`payment_method`, `date_purchased` FROM orders WHERE date_purchased BETWEEN "2013-03-01" AND "2013-03-30"

ps - looks like you just had regular single-quotes (') around the table name instead of back-quotes (`) which was throwing the error

[navido]

Thanks for each one of you.

jc8125:

It worked, however two things

I exported to "CSV for MS excel" and the "orders_id" doesnt show up with the ordernumber, but the text "orders_id". See the picture below



Also when try to use the Autosum in excel, it does not summarize the orders total column. It just shows 0. This is because it doesnt recognize is as integer or number. How to solve this?

[jc8125]

doh! I copied your original code, which had the incorrect quotes around the orders_id field, try this:

SELECT `orders_id`,`customers_name`,`order_total`,`payment_method`, `date_purchased` FROM orders WHERE date_purchased BETWEEN "2013-03-01" AND "2013-03-30"

not sure about the autosum issue.. I don't export as CSV for Excel - so can't provide much help. I just tested using the "export as csv" and it works fine, and autosums things correctly (in excel). might want to use that method

[navido]

Thanks

How about the Autosum. Any idea?

[jc8125]

you might not have seen the edited part of my post, seems to work when exporting as csv (as opposed to csv for excel). not sure what exactly the difference is, probably adds some goofy microsoft formatting. i would just use the plain csv unless you have a reason otherwise.

[navido]

Yes i managed to get it work with CSV by following this step:
http://www.brighthub.com/computing/w...82.aspx#imgn_0

However the column which contains the total "order total" does not get summarized. It just shows 0 when i use the autosum.

[jc8125]

not sure.. when i export as "csv for excel", i have to go through the text-to-columns steps you referenced. however, when i export as plain old csv, it opens up fine in excel, already split into columns correctly, and the total/sum works fine on the order totals. not sure what to tell ya.