?
Solved

Oracle PL-SQL dates

Posted on 2014-11-25
6
Medium Priority
?
475 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
Comment
Question by:saved4use
6 Comments
 
LVL 23

Accepted Solution

by:
Steve Wales earned 1500 total points
ID: 40465835
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 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40465992
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 35

Expert Comment

by:johnsone
ID: 40465997
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'); 

Open in new window

0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LVL 35

Expert Comment

by:johnsone
ID: 40465998
I wouldn't use the D format.  The day number of the week depends on NLS settings.
0
 
LVL 32

Expert Comment

by:awking00
ID: 40466888
>>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 32

Expert Comment

by:awking00
ID: 40470405
saved4use,
Examples?
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

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

In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
Suggested Courses

579 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