Solved

Subtract from System Date (SYSDATE)

Posted on 1998-04-01
3
1,640 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Need help with Oracle syntax 4 56
Oracle - How to analyze data using DATE COLUMN? 7 74
unable to get sorting resultset 15 68
database upgrade 8 41
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example, show how to take different types of Oracle backups using RMAN.

896 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now