Solved

Oracle PL-SQL dates

Posted on 2014-11-25
6
408 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
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.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL join help to a thrid table 51 76
syadmin MSSQL 2 58
Process mapping 5 27
Extract Currency data from a string and put them in a new field 3 17
SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

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