Results 1 to 8 of 8
  1. #1
    Join Date
    May 2012
    Posts
    3

    Unanswered: Getting second sunday of March and first sunday of November

    Hi,

    Could any one help me. My requirement is to get the Second Sunday (date) of March and first Sunday of November of any year using SQL query alone. DB2 version is 9.0.

    Thanks in advance,
    Deepthi

  2. #2
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    You could use a calendar table (and other methods).
    This is a FAQ
    google for it.

  3. #3
    Join Date
    May 2012
    Posts
    3
    didnt find it in google..there were only solutions for oracle/mysqlserver... not in COBOL/DB2/SQL..so i posted

  4. #4
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by deep81this View Post
    didnt find it in google..there were only solutions for oracle/mysqlserver... not in COBOL/DB2/SQL..so i posted
    If you do these kind of calculations on a regular basis it is best to generate a calendar table (as db2mor suggested). If not you can generate on the fly and use it as in:

    Code:
    with cal(d) as ( 
        values date('2012-01-01') 
        union all 
        select d + 1 day from cal 
        where d + 1 day < '2013-01-01'
    ) 
    select d 
    from (
        select d, row_number() over (order by d) as rn
        from cal where month(d) = 3 
         and DAYOFWEEK(d) = 1
    ) where rn = 2
    --
    Lennart

  5. #5
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    As others have said, date table is the best way to go if the requirement is frequent.

    Lennart's approach above is quiet generic .. If your requirement is specific, you can do something like

    Code:
    select date('2013-03-08')+(7-dayofweek_iso(date('2013-03-08'))) days from sysibm.sysdummy1
    
    ;
    
    select date('2012-11-01')+(7-dayofweek_iso(date('2012-11-01'))) days from sysibm.sysdummy1
    
    ;
    This is based on the fact that the first Sunday of a month has to be between 01st and 07th and the second sunday has to be between 8 and 14th. So, you find what day the 01st or 8th is and then add some days to it to find Sunday

    this makes it simpler and better performant.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  6. #6
    Join Date
    Oct 2009
    Location
    221B Baker St.
    Posts
    486

    find dao-of-week for certain dates

    It is rather inconsiderate to post the same question on multiple forums. . .

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by papadi View Post
    It is rather inconsiderate to post the same question on multiple forums. . .
    That depends on how soon the homework assignment is due.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  8. #8
    Join Date
    May 2012
    Posts
    3
    sorry for late reply..

    it worked with
    SELECT DATE(NEXT_DAY(LAST_DAY('2013-02-01'),'SUN') + 7 DAYS)
    ,DATE(NEXT_DAY(DATE('2013-10-31'),'SUN'))
    FROM SYSIBM.SYSDUMMY1
    ;

    and yes, papadi, sorry for being inconsiderate, didnt had much time to go thru each and every post in the forum..but i did try a search with key words, with no luck
    and thanks, Marcus A, for being considerate enuf to understand..

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •