Month of the month (but according to the calendar week)

brauliomsf · Fri, 17 Oct 2008 2:10 pm

Hello
Someone knows would have a function or SQL even if it shows me the week of the month but based on the calendar, that is:

October 2008: week 1 would be of day 1 4
Week 2 would be from day 5 to 11 and so on

using to_char(data_qualquer,'W') shows the month of the month but always breaking from 7 in 7, : week1 (from day 1 to 7), week 2 (from day 8 to 14) .....

grate

Fri, 17 Oct 2008 5:31 pm

Look:
I made a table that contains dates from 1 to the other month:

Select all

select trunc(to_date('01-mar-2008'),'mm')+rownum-1 dt   
      from all_objects 
      where rownum<=35

ie:

Select all

SQL> select trunc(to_date('01-mar-2008'),'mm')+rownum-1 dt 
  2        from all_objects 
  3        where rownum<=35 
  4  / 
 
DT 
--------- 
01-MAR-08 
02-MAR-08 
03-MAR-08 
04-MAR-08 
05-MAR-08 
06-MAR-08 
07-MAR-08 
08-MAR-08 
09-MAR-08 
10-MAR-08 
11-MAR-08 
12-MAR-08 
13-MAR-08 
14-MAR-08 
15-MAR-08 
16-MAR-08 
17-MAR-08 
18-MAR-08 
. 
. 
.

That we have this table, I did like this:

Select all

SELECT  
  dt  
, TO_CHAR( dt, 'DY' , 'NLS_DATE_LANGUAGE=PORTUGUESE' ) dia   
, CASE  
  WHEN dt - 8+TO_CHAR(  TRUNC(dt,'MM'), 'D') <  TRUNC(dt,'MM')  THEN 1 
  ELSE TO_CHAR (dt - 8+TO_CHAR(  TRUNC(dt,'MM'), 'D') , 'W')+1      
  END semana 
 
FROM (SELECT TRUNC(TO_DATE('01-MAR-2008'),'MM')+ROWNUM-1 dt   
      FROM all_objects 
      WHERE ROWNUM<=35 
     )

Let us go to the tests:

Select all

SQL> SELECT  
  2    dt  
  3  , TO_CHAR( dt, 'DY' , 'NLS_DATE_LANGUAGE=PORTUGUESE' ) dia   
  4  , CASE  
  5    WHEN dt - 8+TO_CHAR(  TRUNC(dt,'MM'), 'D') <  TRUNC(dt,'MM')  THEN 1 
  6    ELSE TO_CHAR (dt - 8+TO_CHAR(  TRUNC(dt,'MM'), 'D') , 'W')+1      
  7    END semana 
  8  FROM (SELECT TRUNC(TO_DATE('01-MAR-2008'),'MM')+ROWNUM-1 dt   
  9        FROM all_objects 
 10        WHERE ROWNUM<=35 
 11       ) 
 12  / 
 
DT        DIA           SEMANA 
--------- --------- ---------- 
01-MAR-08 SAB                1 
02-MAR-08 DOM                2 
03-MAR-08 SEG                2 
04-MAR-08 TER                2 
05-MAR-08 QUA                2 
06-MAR-08 QUI                2 
07-MAR-08 SEX                2 
08-MAR-08 SAB                2 
09-MAR-08 DOM                3 
10-MAR-08 SEG                3 
11-MAR-08 TER                3 
 
DT        DIA           SEMANA 
--------- --------- ---------- 
12-MAR-08 QUA                3 
13-MAR-08 QUI                3 
14-MAR-08 SEX                3 
15-MAR-08 SAB                3 
16-MAR-08 DOM                4 
17-MAR-08 SEG                4 
18-MAR-08 TER                4 
19-MAR-08 QUA                4 
20-MAR-08 QUI                4 
21-MAR-08 SEX                4 
22-MAR-08 SAB                4 
 
DT        DIA           SEMANA 
--------- --------- ---------- 
23-MAR-08 DOM                5 
24-MAR-08 SEG                5 
25-MAR-08 TER                5 
26-MAR-08 QUA                5 
27-MAR-08 QUI                5 
28-MAR-08 SEX                5 
29-MAR-08 SAB                5 
30-MAR-08 DOM                6 
31-MAR-08 SEG                6 
01-APR-08 TER                1 
02-APR-08 QUA                1 
 
DT        DIA           SEMANA 
--------- --------- ---------- 
03-APR-08 QUI                1 
04-APR-08 SEX                1 
 
35 rows selected. 
 
SQL>

Let's go to another month (June ):

Select all

SQL> SELECT  
  2    dt  
  3  , TO_CHAR( dt, 'DY' , 'NLS_DATE_LANGUAGE=PORTUGUESE' ) dia   
  4  , CASE  
  5    WHEN dt - 8+TO_CHAR(  TRUNC(dt,'MM'), 'D') <  TRUNC(dt,'MM')  THEN 1 
  6    ELSE TO_CHAR (dt - 8+TO_CHAR(  TRUNC(dt,'MM'), 'D') , 'W')+1      
  7    END semana 
  8  FROM (SELECT TRUNC(TO_DATE('01-jun-2008'),'MM')+ROWNUM-1 dt   
  9        FROM all_objects 
 10        WHERE ROWNUM<=35 
 11       ) 
 12  / 
 
DT        DIA           SEMANA 
--------- --------- ---------- 
01-JUN-08 DOM                1 
02-JUN-08 SEG                1 
03-JUN-08 TER                1 
04-JUN-08 QUA                1 
05-JUN-08 QUI                1 
06-JUN-08 SEX                1 
07-JUN-08 SAB                1 
08-JUN-08 DOM                2 
09-JUN-08 SEG                2 
10-JUN-08 TER                2 
11-JUN-08 QUA                2 
 
DT        DIA           SEMANA 
--------- --------- ---------- 
12-JUN-08 QUI                2 
13-JUN-08 SEX                2 
14-JUN-08 SAB                2 
15-JUN-08 DOM                3 
16-JUN-08 SEG                3 
17-JUN-08 TER                3 
18-JUN-08 QUA                3 
19-JUN-08 QUI                3 
20-JUN-08 SEX                3 
21-JUN-08 SAB                3 
22-JUN-08 DOM                4 
 
DT        DIA           SEMANA 
--------- --------- ---------- 
23-JUN-08 SEG                4 
24-JUN-08 TER                4 
25-JUN-08 QUA                4 
26-JUN-08 QUI                4 
27-JUN-08 SEX                4 
28-JUN-08 SAB                4 
29-JUN-08 DOM                5 
30-JUN-08 SEG                5 
01-JUL-08 TER                1 
02-JUL-08 QUA                1 
03-JUL-08 QUI                1 
 
DT        DIA           SEMANA 
--------- --------- ---------- 
04-JUL-08 SEX                1 
05-JUL-08 SAB                1 
 
35 rows selected. 
 
SQL>

It must probably exist an easier way !!!
This topic always helps me in this hour: http://en.glufke.net/oracle/viewtopic.php?t=22
: -O

margaridi · Tue, 04 Nov 2008 12:01 am

I created a function to help you.
follows below the code of the same and an example of use.
If you have questions, send MSG.
Hugs.

Select all

 
declare 
 
vCurrentWeek number(1); 
vWeek1       number(2); 
vWeek2       number(2); 
vWeek3       number(2); 
vWeek4       number(2); 
vWeek5       number(2); 
 
-------------------------------------------------------------------------- 
-- Function fFindWeek 
-- A partir de uma data qualquer, a função calcula a semana atual, de  
-- acordo com o calendário comercial, sendo que cada semana começa 
-- no domingo e termina no sábado 
-------------------------------------------------------------------------- 
-- Desenvolvedor: Margaridi, Marco Aurélio (marco@margaridi.com) 
-- Data         : 04/11/2008 
-------------------------------------------------------------------------- 
-- Parâmetros: 
 
-- ffw_date_to_test    in  date   -- a data a ser testada 
-- ffw_week_1_last_day out number -- o último dia da primeira semana  
-- ffw_week_2_last_day out number -- o último dia da segunda semana    
-- ffw_week_3_last_day out number -- o último dia da terceira semana    
-- ffw_week_4_last_day out number -- o último dia da quarta semana    
-- ffw_week_5_last_day out number -- o último dia da quinta semana    
                                  -- ou ZERO se o mês só possuir 4 semanas 
-- A função retorna a semana da data informada 
-------------------------------------------------------------------------- 
function fFindWeek(ffw_date_to_test in date 
                  ,ffw_week_1_last_day out number 
                  ,ffw_week_2_last_day out number  
                  ,ffw_week_3_last_day out number 
                  ,ffw_week_4_last_day out number 
                  ,ffw_week_5_last_day out number) return number is 
  vWeekControl    number(1) := 0; 
  vWeekDay        number(1); 
  vMonthDay       number(2); 
  vLastDay        date; 
  vFirstDay       date; 
  vDayToTest      date; 
  vResult         number; 
 
begin 
 
  select last_day(sysdate) 
  into   vLastDay 
  from   dual; 
   
  vFirstDay  := to_date('01'||to_char(vLastDay,'mmyyyy'),'ddmmyyyy'); 
  vDayToTest := trunc(ffw_date_to_test); 
   
  while vFirstDay < vLastDay loop 
   
 
     select to_number(to_char(vFirstDay,'d'),'9') 
     ,      to_number(to_char(vFirstDay,'dd'),'99') 
     into   vWeekDay 
     ,      vMonthDay 
     from   dual; 
      
     if vFirstDay = vDayToTest then 
        vResult := vWeekControl + 1; 
     end if; 
      
     if vWeekDay = 7 then 
      
        vWeekControl := vWeekControl + 1; 
         
        case vWeekControl  
           when 1 then ffw_week_1_last_day := vMonthDay; 
           when 2 then ffw_week_2_last_day := vMonthDay; 
           when 3 then ffw_week_3_last_day := vMonthDay; 
           when 4 then ffw_week_4_last_day := vMonthDay; 
           when 5 then ffw_week_5_last_day := vMonthDay; 
        end case; 
            
     end if; 
   
     vFirstDay := vFirstDay + 1; 
   
  end loop; 
   
  ffw_week_5_last_day := vMonthDay; 
 
  if vWeekControl = 3 then 
     ffw_week_4_last_day := vMonthDay; 
     ffw_week_5_last_day := 0; 
  end if; 
 
  return(vResult); 
 
end ffindweek; 
-------------------------------------------------------------------------- 
-- Function fFindWeek (END) 
-------------------------------------------------------------------------- 
 
begin 
   vCurrentWeek := ffindWeek(sysdate,vWeek1,vWeek2,vWeek3,vWeek4,vWeek5); 
   dbms_output.put_line(to_char(vCurrentWeek)||' | '||to_char(vWeek1) 
                                             ||' | '||to_char(vWeek2) 
                                             ||' | '||to_char(vWeek3) 
                                             ||' | '||to_char(vWeek4) 
                                             ||' | '||to_char(vWeek5)); 
end;

leodamian · Fri, 08 Dec 2023 11:30 am

Topic under translation. Come back later. (This is an automatic message)

Month of the month (but according to the calendar week)

Who is online