Function to return the first Sunday of the month following

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
  

Poston Mon, 14 Apr 2008 4:31 pm

Guys, I need a help to create a function to return the day of the first Sunday of the month following.

Can I pass as parameter in the function the last day of the month to ease development.

My idea is to subtract the value of the corresponding day of the week 8 of the last day of the current month.

For example 4/30/2008 day is 4, so I'm going to add up to 4 4/30/2008 day, which would return day 5/4/2008.

Does anyone have another solution to this problem?
rteramae
Location: São Paulo - SP

RENATO TERAMAE
TMAE CONSULTORIA E INFORMATICA

Poston Mon, 14 Apr 2008 6:39 pm

Since you're in SQL Forum (:P), a solution would be to use TO_CHAR ADD_MONTHS and functions with a lookup table to get the first Sunday, something like:
Code: Select all
select pdia
from   (
         select trunc(add_months(sysdate, 1), 'MM') + b.inc pdia
         from   dual a
              , (
                  select level-1 inc
                  from   dual
                  connect by level <= 7
                ) b
       )
where  to_char(pdia, 'D') = '1'
or also following your reasoning:
Code: Select all
select trunc(add_months(sysdate, 1), 'MM')
     + mod((8 - to_number(to_char(trunc(add_months(sysdate, 1), 'MM'), 'D'))),7)
from dual
rogenaro
Location: Londrina - PR

Rafael O. Genaro

Poston Tue, 15 Apr 2008 8:37 am

Thanks, that's exactly what I was looking for.
rteramae
Location: São Paulo - SP

RENATO TERAMAE
TMAE CONSULTORIA E INFORMATICA


  • See also
    Replies
    Views
    Last Post


Return to SQL

Who is online

Users browsing this forum: No registered users and 3 guests