Solved

Oracle PL-SQL dates

Posted on 2014-11-25
6
413 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 22

Accepted Solution

by:
Steve Wales earned 500 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 76

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 34

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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
LVL 34

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

805 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