Home » SQL & PL/SQL » SQL & PL/SQL » How to write a query for 4th Thursday of November in each year (PL/SQL)
How to write a query for 4th Thursday of November in each year [message #324689] Tue, 03 June 2008 12:57 Go to next message
sbryantma
Messages: 16
Registered: September 2006
Junior Member
Hi,

Is there a way to write a query for showing the 4th Thursday of November (Thanksgiving day) in each year? This is an example of one holiday. I appreciate your responses.

Thanks,
Steve
Re: How to write a query for 4th Thursday of November in each year [message #324691 is a reply to message #324689] Tue, 03 June 2008 12:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes it possible, now show us what you tried.

Regards
Michel
Re: How to write a query for 4th Thursday of November in each year [message #324694 is a reply to message #324689] Tue, 03 June 2008 13:17 Go to previous messageGo to next message
sbryantma
Messages: 16
Registered: September 2006
Junior Member
Hi Michel,

I can't figure it out to show a 4th week of the month of November of this year.

select next_day(last_day(add_months(sysdate, 4)),'thursday') from dual

Thanks,
Steve

Re: How to write a query for 4th Thursday of November in each year [message #324700 is a reply to message #324689] Tue, 03 June 2008 13:31 Go to previous messageGo to next message
sbryantma
Messages: 16
Registered: September 2006
Junior Member

I finally figure it out.
old:
select next_day(last_day(add_months(sysdate, 4)),'thursday') from dual

New:
select next_day(last_day(add_months(sysdate, 4)),'thursday')+21 from dual


My new query shows the correct results.

Thanks,
Steve


Re: How to write a query for 4th Thursday of November in each year [message #324705 is a reply to message #324700] Tue, 03 June 2008 13:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This does not answer your question ("each year"), and I think it should not depend on current date.

Regards
Michel

Re: How to write a query for 4th Thursday of November in each year [message #324709 is a reply to message #324705] Tue, 03 June 2008 14:00 Go to previous messageGo to next message
sbryantma
Messages: 16
Registered: September 2006
Junior Member
Oh!

Here is my query below:

select next_day(last_day(add_months('01-JAN-'||to_char(sysdate, 'YYYY'), 9)),'thursday')+21 from dual

Let me know if you have any questions.

Thanks,
Steve
Re: How to write a query for 4th Thursday of November in each year [message #324711 is a reply to message #324689] Tue, 03 June 2008 14:02 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>Let me know if you have any questions.
On what date is Thanksgiving in 2009, 2010, etc.?
Re: How to write a query for 4th Thursday of November in each year [message #324712 is a reply to message #324709] Tue, 03 June 2008 14:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
TRUNC can give you the first day of the year without these implicit and explicit conversions that do not work in all cases.

Regards
Michel

[Updated on: Tue, 03 June 2008 14:04]

Report message to a moderator

Re: How to write a query for 4th Thursday of November in each year [message #324713 is a reply to message #324712] Tue, 03 June 2008 14:15 Go to previous message
sbryantma
Messages: 16
Registered: September 2006
Junior Member
Yeah, I forgot about that. I will try it tomorrow to use the trunc function as the first day of the first.

Thanks for helping me out.
Steve
Previous Topic: Optimizing this query
Next Topic: Problem: Run schedular to copy from one table to other
Goto Forum:
  


Current Time: Thu Dec 08 04:11:52 CST 2016

Total time taken to generate the page: 0.06415 seconds