Solved

# Oracle PL-SQL dates

Posted on 2014-11-25
402 Views
Last Modified: 2014-12-04
I'd like to calculate the last 4 weeks (only business days Mon-Fri), but the code below gives me the last 4 weeks starting from 10/22, instead of 10/27:
p.fnd_complete_dt >=  trunc(sysdate - 28,'WW') and p.fnd_complete_dt <  trunc(sysdate,'WW')
I also need to do the same for the last 12 weeks:
p.fnd_complete_dt >=  trunc(sysdate - 84,'WW') and p.fnd_complete_dt <  trunc(sysdate,'WW')

Further, I am trying to calculate the business days (Mon-Fri) between two dates. Is there a way to do it better than my code below which would include weekends?
(a.fnd_complete_dt - a.appvl_dt).

Thanks for your tremendous assistance.
0
Question by:saved4use
6 Comments

LVL 22

Accepted Solution

Steve Wales earned 500 total points
I can help you with the TRUNC part of the question.

TRUNC with a parameter of 'WW', truncates that date to the week that starts on the same day of the week as the first of the year.

January 1, 2014 was a Wednesday.

So, TRUNC(sysdate - 28), 'WW') will return you the Wednesday of the week you are in.

See the docs:  https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions230.htm#i1002084

If you are looking for the date part without the time, try trunc(sysdate)-28.

The other part, I'll have to defer on someone else to do.
0

LVL 76

Expert Comment

The format mask of 'D' gives you the day of week (1-7).

Not having sample data or expected results, maybe all you need to do is exclude 1 and 7 (Sat and Sun) from the results?

...
and to_char(p.fnd_complete_dt.'D') not in (2,3,4,5,6)
...
0

LVL 34

Expert Comment

In order to get a Monday, you would back it up an additional week and look for the next Monday, so it would be something like this:

p.fnd_complete_dt >=  next_day(trunc(sysdate) - 35, 'Monday') and p.fnd_complete_dt <  next_day(trunc(sysdate) - 7, 'Saturday')

Then you would have to add this to exclude Saturday and Sundays:

and trim(to_char(p.fnd_complete_dt, 'Day')) not in ('Saturday','Sunday')

Not exactly sure on how to get you the date difference.  I know some people have functions/formulas for that so I will defer and let them post those.

However, this may help you.  This query will generate a list of the days in the time period.  You may be able to join to that and create a count.

``````SELECT Next_day(Trunc(SYSDATE) - 28, 'Monday') + ( LEVEL - 1 )
FROM   dual
WHERE  Trim(To_char(Next_day(Trunc(SYSDATE) - 28, 'Monday') + ( LEVEL - 1 ),
'Day'))
NOT IN ( 'Saturday', 'Sunday' )
CONNECT BY Next_day(Trunc(SYSDATE) - 28, 'Monday') + ( LEVEL - 1 ) <=
Next_day(Trunc(SYSDATE) - 7, 'Friday');
``````
0

LVL 34

Expert Comment

I wouldn't use the D format.  The day number of the week depends on NLS settings.
0

LVL 31

Expert Comment

>>I'd like to calculate the last 4 weeks<<
I'm not sure what you mean by calculate. Are you trying to determine the specific dates in that period or when that period began or something else (e.g. the number of weekdays)? Do you mean specifically 4 weeks ago or the first Monday from 4 weeks ago? Does it include partial weeks or you only want the 4 previous whole weeks? Are you concerned with holidays? If you could provide some sample data and what results you expect, it would probably help clarify your intent and provide the experts with a test case.
0

LVL 31

Expert Comment

saved4use,
Examples?
0

## Join & Write a Comment Already a member? Login.

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
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…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

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

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

#### Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!