[Tip]-week of the year

Dúvidas, dicas e truques de PL/SQL. Aqui também vão assuntos relacionados a pacotes, triggers, funções, Java-Stored Procedures, etc
  

Poston Wed, 07 Dec 2005 3:24 pm

Running the following query, will bring the week of the year incorrectly ...
Code: Select all
SQL> SELECT SYSDATE, to_char(SYSDATE,'WW') semana_ano
  2  FROM dual;

SYSDATE     SEMANA_ANO
----------- ----------
07/12/2005  49
this is because the Oracle takes into account the day January 01 until 07 January as the first week, but in many cases when January 01 is on a Saturday as in the year 2005, day 2 of January is week 2 of the year ... so I created a function that calculates correctly the week of the year taking into account that the week starts on Sunday.
Code: Select all
SQL> CREATE OR REPLACE FUNCTION fun_semana_ano(prm_data IN DATE) RETURN NUMBER IS
  2    wrk_nr_semana_ano  NUMBER;
  3    wrk_nr_dia_semana1 NUMBER;
  4    wrk_nr_dia_semana2 NUMBER;
  5  BEGIN
  6    --pego o dia da semana do primeiro dia do ano.
  7    wrk_nr_dia_semana1 := to_number(to_char(to_date('01-jan-'||to_char(prm_data,'RRRR')),'D'));
  8    --pego a semana do ano do Oracle
  9    wrk_nr_semana_ano  := to_number(to_char(prm_data,'WW'));
10    --pego o dia da semana do parâmetro
11    wrk_nr_dia_semana2 := to_number(to_char(prm_data,'D'));
12    --se o dia da semana do parâmetro for menor que o dia da semana do dia 01/jan
13    --então acrescento mais um a semana do ano do Oracle
14    IF wrk_nr_dia_semana2 < wrk_nr_dia_semana1 THEN
15      wrk_nr_semana_ano := wrk_nr_semana_ano + 1;
16    END IF;
17    --
18    RETURN(wrk_nr_semana_ano);
19  END;
20  /

Function created

SQL> SELECT SYSDATE, fun_semana_ano(SYSDATE) semana_ano
  2  FROM dual;

SYSDATE     SEMANA_ANO
----------- ----------
07/12/2005          50
Tai, I hope it will be useful to someone ...

[]´s

:-o
anderson
Location: Toledo - PR

Anderson Nuernberg
---


Return to PL/SQL

Who is online

Users browsing this forum: No registered users and 2 guests