Hesitate to select with holidays and weekends

[carlos12]

Good morning people.

I have a question that I think is BRaba I need to make a select that brings the Sundays and holidays of the year 2007.

In the Sundays and holidays of 2007 in Oracle I have the field date that has the normal dates

personal I need a lot of this help.

[Trevisolli]

Brother,
Good morning.

To get Saturdays and Sundays, quiet:

Select all

 
SELECT TO_CHAR(SYSDATE,'DY') FROM DUAL; 

Hence, make sure it is Saturday or Sunday in this return.
As for the holidays, then you have to have a registration on your system, okay?
Whatever, send it there.

[carlos12]

Trevisolli

Let's go by parts. First Sunday

As I can in a select differentiate type only bring the Sundays type

Select all

SELECT TO_CHAR(u.dtinicio,'DY') FROM usuario u  
where u.dtinicio like 'DOM'; 

I tried this but not Can not you ??

[Trevisolli]

Brother, come on!

To get Saturday or Sunday (watch for the bank configuration, in case, here in English, therefore, Saturday = Sat (Saturday) and Sunday = Sun (Sunday), beleza? ).

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 do the tests there, change the date of 04/13/2008, which is a Sunday, by the date the user provide you, beleza?

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

Whatever, send it there.

[carlos12]

Legal boy but looks only I need this for a select not to insert anything in bd type the user would enter with a rang of dates type

Select all

u.dtinicio between '&data_INI' and '&data_fim'

there Select only brings dates related to Sunday, my dates are in Portuguese type when I did:

Select all

SELECT TO_CHAR(u.dtinicio,'DY') FROM usuario u 

He returned
SAB
DOM
chi

and so on

[Trevisolli]

Brother,

From what I understand, the user will enter a range of dates and, you want to show him only business days, is that it?

[carlos12]

This user types the date ex.01 / 01/2007 on 04/30/2007 right

ai this select only returns the domains only the dates regarding Sunday

[Trevisolli]

Ai brother, do a tests:
in pl / sql :

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, with sql It is also possible:

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'); 

whatever, send it there.

[carlos12]

Trevisolli
face I apologize but I'm kind of slow let's go on the part where I put my table on this train? rs

Table = User
Field = Dtinicio

And in that part you put

99] - Do something here.

What is it to do?

face gets pissed no but is that I need it ...

strong hug

[Trevisolli]

Brother,

Do with SQL I added in the previous code.
has the two forms, but, that of SQL meets your need, what would be to give a select on a table, returning only Sundays, beleza?

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');  

Whatever, send it, without crisis.

[carlos12]

Trevisolli


HHUHUHAUAHSUAHUAA Cade gave right


I did the SQL that was underneath and I in a hurry did not even see kkkk dude, I owe you this. Mano Bigadão.

God you page!

[carlos12]

Trevisolli


HHUHUHAUAHSUAHUAA Cade gave right


I did the SQL that was underneath and I in a hurry did not even see kkkk dude, I owe you this. Mano Bigadão.

[carlos12]

Trevisolli


HHUHUHAUAHSUAHUAA Cade gave right


I did the SQL that was underneath and I in a hurry did not even see kkkk dude, I owe you this. Mano Bigadão.

[Trevisolli]

beleza BROTHER, good!
Needing, count on us.

[Toad]

http://en.glufke.net/oracle/search.php
I believe that this topic can also help:
http://en.glufke.net/oracle/viewtopic.p ... as+%FAteis