Solved

Oracle PL-SQL dates

Posted on 2014-11-25
6
438 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 77

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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
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

The Orion Papers

Are you interested in becoming an AWS Certified Solutions Architect?

Discover a new interactive way of training for the exam.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

717 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