SQL Query to fetch data from the last 30 days?
The easiest way would be to specify
SELECT productid FROM product where purchase_date > sysdate-30;
Remember this sysdate above has the time component, so it will be purchase orders newer than 03-06-2011 8:54 AM based on the time now.
If you want to remove the time conponent when comparing..
SELECT productid FROM product where purchase_date > trunc(sysdate-30);
And (based on your comments), if you want to specify a particular date, make sure you use to_date and not rely on the default session parameters.
SELECT productid FROM product where purchase_date > to_date('03/06/2011','mm/dd/yyyy')
And regardng the between (sysdate-30) - (sysdate) comment, for orders you should be ok with usin just the sysdate condition unless you can have orders with order_dates in the future.
Pay attention to one aspect when doing "purchase_date>(sysdate-30)": "sysdate" is the current date, hour, minute and second. So "sysdate-30" is not exactly "30 days ago", but "30 days ago at this exact hour".
If your purchase dates have 00.00.00 in hours, minutes, seconds, better doing:
where trunc(purchase_date)>trunc(sysdate-30)
(this doesn't take hours, minutes and seconds into account).