Home » SQL & PL/SQL » SQL & PL/SQL » find out second sunday in March
find out second sunday in March [message #212508] Fri, 05 January 2007 11:03 Go to next message
yerics
Messages: 89
Registered: August 2006
Member
My db version is 9.2.0.5 and I want to find the 2nd Sunday in March and 1st Sunday in November for any year. Basically this is to factor in daylight saving and buiild other processing logic.

Regards,
Re: find out second sunday in March [message #212509 is a reply to message #212508] Fri, 05 January 2007 11:07 Go to previous messageGo to next message
anilsinare
Messages: 22
Registered: December 2005
Location: ipswich, uk
Junior Member
Hi, I hope below query will help you.

select
next_day(next_day('01-Mar-2006','SUNDAY'),'SUNDAY') second_sunday_in_march,
next_day(next_day('01-Nov-2006','SUNDAY'),'SUNDAY') forst_sunday_in_Nov
from dual

Regards,
Anil Sinare
Re: find out second sunday in March [message #212511 is a reply to message #212509] Fri, 05 January 2007 11:11 Go to previous messageGo to next message
yerics
Messages: 89
Registered: August 2006
Member
Thanks for the SQL. However this specifically deals with year 2006. I want it for make it generalized (any year). find 2nd sunday in March for any year. not 2006 specific.

Regards,
Re: find out second sunday in March [message #212515 is a reply to message #212511] Fri, 05 January 2007 11:24 Go to previous messageGo to next message
anilsinare
Messages: 22
Registered: December 2005
Location: ipswich, uk
Junior Member
Use following query -

select
No For_Year,
next_day(next_day(to_date('01-Mar-'||to_char(no)||'','dd-Mon-yyyy'),'SUNDAY'),'SUNDAY') second_sunday_in_march,
next_day(next_day(to_date('01-Mar-'||to_char(no)||'','dd-Mon-yyyy'),'SUNDAY'),'SUNDAY') forst_sunday_in_Nov
from (select rownum+2000 no from user_objects);

This will show you the years from 2001. If you want to change this just change the figure "select rownum+2000 no from user_objects" in this query.

Regards,
Anil Sinare

[Updated on: Fri, 05 January 2007 11:25]

Report message to a moderator

Re: find out second sunday in March [message #212516 is a reply to message #212508] Fri, 05 January 2007 11:28 Go to previous messageGo to next message
yerics
Messages: 89
Registered: August 2006
Member
Thanks Again. However for year 2009, 15th March is the 3rd Sunday and not the 2nd one. Is there a way where this can be corrected?

Regards,
Re: find out second sunday in March [message #212517 is a reply to message #212515] Fri, 05 January 2007 11:29 Go to previous messageGo to next message
yerics
Messages: 89
Registered: August 2006
Member
Same is the case for 2015 as well.I did not go beyond that.

Regards,
Re: find out second sunday in March [message #212523 is a reply to message #212517] Fri, 05 January 2007 12:02 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Yes, because every 7 years or so the first day '1-Mar' is a Sunday itself, so the next-next sunday is the third Sunday in the Month.

So it should not be the next-nest sunday from '1-Mar', but the next-next Sunday from '1-Mar' - 1

Just add that -1 after the closing bracket of the innermost to_date.
Re: find out second sunday in March [message #212524 is a reply to message #212523] Fri, 05 January 2007 12:09 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Something like this?
WITH mar_nov AS
  (SELECT ADD_MONTHS(TRUNC(SYSDATE, 'year'), 2) fom,
          ADD_MONTHS(TRUNC(SYSDATE, 'year'), 10) fon 
   FROM dual
  )
SELECT NEXT_DAY(fom, 'NED') + 7 second_sunday_in_march,
       NEXT_DAY(fon, 'NED') first_sunday_in_november
FROM mar_nov;
Re: find out second sunday in March [message #212525 is a reply to message #212508] Fri, 05 January 2007 12:10 Go to previous message
yerics
Messages: 89
Registered: August 2006
Member
Thanks.. I got it.

Regards,
Previous Topic: Breaking down a string into parts
Next Topic: populating data from temp tables
Goto Forum:
  


Current Time: Sat Dec 03 18:17:55 CST 2016

Total time taken to generate the page: 0.05850 seconds