Pulling data from a db

problem with dates

[mattyb515]

I am using osCommerce as an online shopping cart and am looking to pull data from the table orders. I'm looking to write a script that'll run everyday, say at 1am, and export the previous days orders to a csv file. I'm having a bit of difficulty with the date aspect of the select statement. I've tried using NOW(), CURDATE() and then using the INTERVAL -1 DAY but nothing seems to work. I was testing the SQL statement through phpMyAdmin but it doesn't return any values so as of right now I'm stuck. Any help with this would be greatly appreciated. Here's what I have for the SELECT statement:

SELECT * FROM orders WHERE date_purchased = CURDATE()

date_purchased is a field in the table but I was wondering if for some reason it's attempting to match the exact time the order was placed with the current time and that's why it's not returning any results. Any thoughts?

[eelixduppy]

Hello...

Are you sure that the dates stored in the databases are using the same date format as the date that the function returns? i would say check this before going crazy.

Hope this helps

eelix

[mattyb515]

The date is stored in this format:

2006-02-14 11:37:45

Didn't even think to check that. So I'm guessing I have to format the date.

[eelixduppy]

hi...again...

Query mysql with the following:

SELECT CURDATE();

This will show you the format that you should store your date in your table.

eelix

[eelixduppy]

If you would like to have a time component then you should use NOW()

eelix

[mattyb515]

Thanks for the help. I actually came up with this:

SELECT * FROM orders WHERE (
EXTRACT(MONTH FROM date_purchased) = EXTRACT(MONTH FROM CURDATE())
AND
DAYOFMONTH(date_purchased) = (DAYOFMONTH(CURDATE()) - 1)
)

and it worked. I'm sure there's an easier way but I'm no genius with php so for now it works.

[stajer]

This is a sql issue, not php. Your code will work, but less overhead with:

select * from orders where date_purchased between now() and dateadd(hh,-24,getdate())