CODEWITHSUNDEEP
oracle-databasedatetime
miracle
miracle

Reputation: 535

SQL Query to fetch data from the last 30 days?

Hi I am new to Oracle. How do I do a simple statement, for example get product id from the last 30, or 20 days purchase date?

SELECT productid FROM product
WHERE purchase_date ?

Upvotes: 52

Views: 216783

Answers (6)

srinivas
srinivas

Reputation: 1

select status, timeplaced 
from orders 
where TIMEPLACED>'2017-06-12 00:00:00'

Upvotes: -1

Faisal
Faisal

Reputation: 4765

Try this : Using this you can select data from last 30 days

SELECT
    *
FROM
    product
WHERE
    purchase_date > DATE_SUB(CURDATE(), INTERVAL 1 MONTH)

Upvotes: 2

sinethr
sinethr

Reputation: 9

SELECT COUNT(job_id) FROM jobs WHERE posted_date < NOW()-30;

Now() returns the current Date and Time.

Upvotes: 0

friol
friol

Reputation: 7096

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).

Upvotes: 10

Rajesh Chamarthi
Rajesh Chamarthi

Reputation: 18818

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.

Upvotes: 19

jgrabowski
jgrabowski

Reputation: 1531

SELECT productid FROM product WHERE purchase_date > sysdate-30

Upvotes: 84

Related Questions