Doubt in select with feriádos and weekends

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

Poston Tue, 08 Apr 2008 9:13 am

Good day folks.

I have a doubt that I think is mad gotta do a select that bring the Sundays and holidays of the year for 2007.

I want to type all that record took place on Sundays and holidays from 2007 at oracle have the date field which has the normal dates guys really need their help.
carlos12
Location: rio

Poston Tue, 08 Apr 2008 9:15 am

Brother, good morning.

For Saturdays and Sundays, quiet:
Code: Select all
SELECT TO_CHAR(SYSDATE,'DY') FROM DUAL;
Hence, check if it is Saturday or Sunday in this return.
As for the holidays, then you have to have an account on your system, right?
qualquer thing, says ai.
Trevisolli
Location: Araraquara - SP

Abraço,

Trevisolli
OCA Oracle PL/SQL Developer Certified Associate
OCP Oracle Forms Developer Certified Professional
Araraquara-SP

Poston Tue, 08 Apr 2008 10:36 am

Trevisolli. first Sunday as I can in a select differentiate guy bring Sundays type SELECT TO_CHAR (u. dtinicio, ' DY ') FROM user u where u. dtinicio like ' GIFT ';

tried so but gave no way do??
carlos12
Location: rio

Poston Tue, 08 Apr 2008 10:43 am

Brother, come on!

To get the Saturday or Sunday (watch out for the setting of the Bank in this case here, in English, therefore, Saturday = Sat (saturdary) and Sunday = Sun (Sunday), beleza?).
Code: Select all
DECLARE
  vdia VARCHAR2(03);
BEGIN
  SELECT TO_CHAR (TO_DATE ('13/04/2008', 'dd/mm/rrrr'), 'DY')
    INTO vdia
    FROM DUAL;
  IF vdia IN ('SAT','SUN')
  THEN
     raise_application_error(-20001, 'Data não permitida: '||vdia||' pois não é um dia útil.');
  ELSE
     dbms_output.put_line('Dia válido. '||vdia);   
  END IF; 
END; 
to make the tests there, change the date of 4/13/2008, which is a Sunday, the date that the user provide, beleza?

As for the holidays, you must have a registration in your system, so only apply the rule above, when is Saturday, Sunday or a holiday of your table, beleza?

qualquer thing, says ai.
Trevisolli
Location: Araraquara - SP

Abraço,

Trevisolli
OCA Oracle PL/SQL Developer Certified Associate
OCP Oracle Forms Developer Certified Professional
Araraquara-SP

Poston Tue, 08 Apr 2008 10:51 am

Boy that's nice but I need it for a select not to insert anything in bd type the user would enter with a rang of u type dates. dtinicio between ' data_INI ' and ' & & data_fim ' ai the select only brings dates related to Sunday, my dates are in Portuguese when I did type: SELECT TO_CHAR (u. dtinicio, ' DY ') FROM user u he returned SAT SUN THU and so on
carlos12
Location: rio

Poston Tue, 08 Apr 2008 11:01 am

Brother, as I understand it, the user will enter a Range of dates, and you want to show him only working days, is it?
Trevisolli
Location: Araraquara - SP

Abraço,

Trevisolli
OCA Oracle PL/SQL Developer Certified Associate
OCP Oracle Forms Developer Certified Professional
Araraquara-SP

Poston Tue, 08 Apr 2008 11:11 am

that the user type the date e.g. 1/1/2007 to 4/30/2007 right there that select only returns the Cakes only dates for the Sunday
carlos12
Location: rio

Poston Tue, 08 Apr 2008 11:36 am

Oh brother, make some tests: In PL/SQL:
Code: Select all
--Ex.01/01/2007 à 30/04/2007

DECLARE
  vdata_ini DATE := TO_DATE('01/01/2007','dd/mm/rrrr');
  vdata_fim DATE := TO_DATE('30/04/2007','dd/mm/rrrr');
  vdias     PLS_INTEGER := 0;
BEGIN
  vdias := (vdata_fim - vdata_ini);
  FOR x IN 1..vdias
  LOOP
    IF TO_CHAR(vdata_ini, 'DY') = 'SUN' -- No teu caso, 'DOM'
    THEN
      -- Faça algo aqui.
      dbms_output.put_line('Imprime a data na tela (Domingo): ' || TO_CHAR(vdata_ini, 'dd/mm/rrrr'));
    END IF;   
    vdata_ini := vdata_ini + 1;
  END LOOP;
END;
or SQL is also possible:
Code: Select all

SELECT *
  FROM sua_tabela
WHERE to_char(dta_atualizacao,'DY') = 'SUN' -- No teu caso, DOM
   AND dta_atualizacao BETWEEN TO_DATE('01/01/2007','dd/mm/rrrr')
                           AND TO_DATE('30/04/2007','dd/mm/rrrr');
qualquer thing, says ai.
Trevisolli
Location: Araraquara - SP

Abraço,

Trevisolli
OCA Oracle PL/SQL Developer Certified Associate
OCP Oracle Forms Developer Certified Professional
Araraquara-SP

Poston Tue, 08 Apr 2008 11:43 am

Trevisolli Guy I apologize but I am a little slow even Let's on the part where I put my table on this train? rs = usuario table field = dtinicio and that part you put THEN-do something here.

What is to do?

Guy gets angry but I'm needing.

Strong hug
carlos12
Location: rio

Poston Tue, 08 Apr 2008 11:46 am

Brother, Do with the SQL that I added in the preceding code.
Has both ways, but the best meets your need SQL, which would give a SELECT on a table, returning only Sundays, beleza?
SELECT * FROM sua_tabela WHERE to_char (dta_atualizacao, ' DY ') = ' SUN '--in your case, DOM AND dta_atualizacao BETWEEN TO_DATE (' 1/1/2007 ', ' dd/mm/rrrr ') AND TO_DATE (' 4/30/2007 ', ' dd/mm/rrrr ');

qualquer thing, send there, without crisis.
Trevisolli
Location: Araraquara - SP

Abraço,

Trevisolli
OCA Oracle PL/SQL Developer Certified Associate
OCP Oracle Forms Developer Certified Professional
Araraquara-SP

Poston Tue, 08 Apr 2008 11:48 am

Trevisolli hhuhuhauuahsuahuaa CADE GAVE CERTINHO VEI did the SQL that was underneath and I in a hurry even seen kkkk guy's corretissimo owe you BRO bigadão.

God you page!
carlos12
Location: rio

Poston Tue, 08 Apr 2008 11:48 am

Trevisolli hhuhuhauuahsuahuaa CADE GAVE CERTINHO VEI did the SQL that was underneath and I in a hurry even seen kkkk guy's corretissimo owe you BRO bigadão.
carlos12
Location: rio

Poston Tue, 08 Apr 2008 11:50 am

Trevisolli hhuhuhauuahsuahuaa CADE GAVE CERTINHO VEI did the SQL that was underneath and I in a hurry even seen kkkk guy's corretissimo owe you BRO bigadão.
carlos12
Location: rio

Poston Tue, 08 Apr 2008 11:50 am

beleza brother, q good!
Need, count on us.
Trevisolli
Location: Araraquara - SP

Abraço,

Trevisolli
OCA Oracle PL/SQL Developer Certified Associate
OCP Oracle Forms Developer Certified Professional
Araraquara-SP

Poston Tue, 22 Apr 2008 1:39 pm

search.php I believe this topic also can help: viewtopic.php?t=650&highlight=dias+%FAteis
Toad
Location: Seattle, WA

Matheus H. Gonçalves
www.toad.com.br
www.twitter.com/toadgeek


  • See also
    Replies
    Views
    Last Post


      Return to SQL

      Who is online

      Users browsing this forum: Google [Bot] and 7 guests