-
(Oracle) Data between 2 dates
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
-
select * from yourtable
where activatedate between add_months(sysdate,-1) and sysdate
-
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!!
-
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
-
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 !!
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|