Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Subtract from System Date (SYSDATE)

Posted on 1998-04-01
3
Medium Priority
?
1,703 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 400 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Suggested Courses

824 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