Solved

Subtract from System Date (SYSDATE)

Posted on 1998-04-01
3
1,656 Views
Last Modified: 2008-03-17
Hi I wanted to do a sql statement that pick entries between a certain date.  I wanted to get all the records that were less than the current date and that were > currentdate - 7.

Is there a way to do this with oracle or do I have to do it on
the programmers side, I am using ASP for the language

  select * from ennadm.menu, ennadm.cafetr where menu.cafetr_no = cafetr.cafetr_no and
  menu.wk_begin_dt < SYSDATE and ????


Thanks for any help.
Arthur
0
Comment
Question by:vbguy
3 Comments
 
LVL 2

Accepted Solution

by:
frankr earned 100 total points
ID: 1082970
Replace the ???? in your statement with

menu.wk_begin_dt > SYSDATE - 7

You can perform date arithmetic directly by adding or subtracting from a date in days.  
Note that the ADD_MONTHS function is available to deal with the situation where the number of days might be variable (e.g. if you need to add 1 year to a date, and don't want to worry about calculating for leap years explicitly).
0
 

Author Comment

by:vbguy
ID: 1082971
Thanks for your help
0
 

Expert Comment

by:ananth
ID: 1082972
Elegent programming would be
menu.wk_begin_dt between sysdate-7  and sysdate.
This is commonly used to find the product/service prices on date of sales from a product/services price table. Hope this helps
0

Featured Post

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that useā€¦
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question