Solved

ORACLE previous week date function

Posted on 2012-03-14
3
695 Views
Last Modified: 2012-03-15
I'd like to create a report which will run every Monday morning showing data for the previous week; Sun - Sat.

CLOSED_DTTM > trunc(SYSDATE)- 7 is not working.

What should I use?

Thanks.
0
Comment
Question by:saved4use
3 Comments
 
LVL 20

Accepted Solution

by:
flow01 earned 500 total points
ID: 37722881
WHERE CLOSED_DDTM >=  trunc(sysdate - 7,'WW')
AND    CLOSED_DDTM <  trunc(sysdate,'WW')

The WW truncation gives as start of the week: sunday 00.00 hrs
0
 

Author Comment

by:saved4use
ID: 37723006
Will the above work even when, say,  the middle of the week is a new month?
0
 
LVL 16

Expert Comment

by:Wasim Akram Shaik
ID: 37723391
yes author, the solution which flow01 will work fine even in case the middle of week is a new month...

what in actual it does it, it truncates the day to start of the week,..

for example 01/Mar/2012 is thursday,

select trunc(to_date('01-Mar-2012','DD-Mon-YYYY'),'WW') from dual

this will result in 26th Feb 2012, which is start of the week and by default the start of week in oracle is sunday..
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

Suggested Solutions

Title # Comments Views Activity
ORA-02288: invalid OPEN mode 2 81
error in my cursor 5 50
update statement in oracle 9 38
oracle query 3 26
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
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.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

740 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