(Oracle) Data between 2 dates

[Spence23]

Hi,

(ORACLE) I am given a date variable of 'ActivateDate' and I need to create a query in which I only get back data where the 'ActivateDate' is in the previous month from the current sysdate.

For example, if I run the query now I need to get data back in which the 'ActivateDate' is between April 1 12:00:00 AM and April 30 11:59:59 PM. Any suggestions on how to do this as I am not sure of the syntax?

Thanks

[skhanal]

select * from yourtable
where activatedate between add_months(sysdate,-1) and sysdate

[oraclable]

the first day and last day of the previous month can be calculated like this :
firstday = last_day(add_months(CURRENT_DATE,-2))+1
and
lastday = last_day(add_months(CURRENT_DATE,-1))

So, your query should be:
select field_name from your_table
where ActivateDate between last_day(add_months(CURRENT_DATE,-2))+1 and last_day(add_months(CURRENT_DATE,-1));




Good luck!!

[Spence23]

Thats just about right, but how would I go about getting the date to the correct time? I need it from 12:00:00 AM on the first day and 11:59:59 PM on the last.

Thanks

[oraclable]

The TRUNC function Converts a date to the date at midnight

So,
select field_name from your_table
where ActivateDate >= trunc(last_day(add_months(CURRENT_DATE,-2))+1) and ActivateDate < trunc(last_day(add_months(CURRENT_DATE,-1))+1);



good luck !!