Home » SQL & PL/SQL » SQL & PL/SQL » How to find Nth weekday from current date? (Oracle 11g )
How to find Nth weekday from current date? [message #631073] Thu, 08 January 2015 16:42 Go to next message
Oracle_Walker
Messages: 71
Registered: January 2012
Location: United States
Member

Hi,

I have a requirement to find 5th business day from current date. For example, If current date is 01/08/2015 Thursday then my output would be 01/01/2015 Thursday (5th business day from sysdate). Please help.
Re: How to find Nth weekday from current date? [message #631074 is a reply to message #631073] Thu, 08 January 2015 17:09 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
select sysdate + 7 "5th_business_day" from dual;
Re: How to find Nth weekday from current date? [message #631075 is a reply to message #631074] Thu, 08 January 2015 17:41 Go to previous messageGo to next message
Oracle_Walker
Messages: 71
Registered: January 2012
Location: United States
Member

Thanks BlackSwan. Does it consider holidays as well?
Re: How to find Nth weekday from current date? [message #631076 is a reply to message #631075] Thu, 08 January 2015 17:54 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Oracle_Walker wrote on Thu, 08 January 2015 15:41
Thanks BlackSwan. Does it consider holidays as well?


Of course it does not consider Holidays because your initial post said NOTHING about holidays!

post crappy & incomplete requirements & you get crappy & incomplete solutions.

What is a Holiday for you may be a normal work day for me.

> If current date is 01/08/2015 Thursday then my output would be 01/01/2015 Thursday (5th business day from sysdate). Please help.
How does a calendar ever go backwards when you start with 01/08/2015 & the answer is 01/01/2015?

Is 01/08/2015 January 8 or August 1, be cause as post it is 100% ambiguous!

What date is 10/11/12?
I'll give you six guesses because your first 5 answer will be declared to be wrong.
Re: How to find Nth weekday from current date? [message #631079 is a reply to message #631076] Thu, 08 January 2015 21:56 Go to previous messageGo to next message
Oracle_Walker
Messages: 71
Registered: January 2012
Location: United States
Member

Sorry Blackswan for not providing exact date format which I have used as example. I have used mm/dd/yyyy as format in the example. My requirement was only to cover weekdays, I was curious to know if that consider holiday. Sorry for the stupid question. Your answer is accurate and I have changed as given below to calculate 5th business day in backwards.
Re: How to find Nth weekday from current date? [message #631101 is a reply to message #631079] Fri, 09 January 2015 04:18 Go to previous messageGo to next message
jimit_shaili
Messages: 237
Registered: June 2006
Location: India, Ahmedabad
Senior Member
Dear Oracle_Walker

This may solve your query.

Note : Saturday and Sunday are considered as holidays. (Non business days)

select max(dt) as business_day_5th from
(
	select dt from
	(
		select trunc (sysdate, 'dd') + level - 1 as dt,to_char(trunc(sysdate, 'dd') + level - 1,'d')  wd from dual connect by level < = 8
	)
	where wd not in(1,7)
)
/


Regards

Jimit
Re: How to find Nth weekday from current date? [message #631115 is a reply to message #631101] Fri, 09 January 2015 09:46 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Do not work for everyone:
SQL> select to_char(sysdate+1,'Day DD-MON-YYYY')||' -> d='||to_char(sysdate+1,'d') from dual
  2  union all
  3  select to_char(sysdate+2,'Day DD-MON-YYYY')||' -> d='||to_char(sysdate+2,'d') from dual
  4  /
TO_CHAR(SYSDATE+1,'DAYDD-MON
----------------------------
Saturday  10-JAN-2015 -> d=6
Sunday    11-JAN-2015 -> d=7


Re: How to find Nth weekday from current date? [message #631178 is a reply to message #631115] Fri, 09 January 2015 23:00 Go to previous messageGo to next message
jimit_shaili
Messages: 237
Registered: June 2006
Location: India, Ahmedabad
Senior Member
Dear Michel

Not quiet clear, what you wanna say.

Quote:

Do not work for everyone


Regards

Jimit
Re: How to find Nth weekday from current date? [message #631186 is a reply to message #631178] Sat, 10 January 2015 00:30 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Didn't you see the result of the query I posted?

Previous Topic: Generate flat file size more than 32767
Next Topic: how to run PLSQL procedure in foreground in SQL command prompt
Goto Forum:
  


Current Time: Fri Mar 29 08:49:43 CDT 2024