Lidt tidsregning i SQL

Denne her kan bruges - du kan så bare lave din egen procedure med måned som input og fromdate - todate som output ....

CREATE OR FUNCTION TotWeekDays( FromDate DATE ,
                                        ToDate  DATE )
RETURN NUMBER IS

  ToTalSunDays    NUMBER := NEXT_DAY(ToDate - 7,'SUNDAY') -
                            NEXT_DAY(FromDate - 1,'SUNDAY') ;

  ToTalSaturDays  NUMBER := NEXT_DAY(ToDate - 7,'SATURDAY') -
                            NEXT_DAY(FromDate - 1,'SATURDAY') ;

BEGIN

  RETURN (ToDate - FromDate - (TotalSundays+TotalSaturdays)/7 -1) ;

END ;

Executing this function in a pl/sql block...

SQL> SET SERVEROUTPUT ON

SQL> DECLARE
  2    BusinessDays NUMBER;
  3  BEGIN
  4    BusinessDays := TotWeekDays('01-MAY-2001','31-MAY-2001') ;
  5    DBMS_OUTPUT.PUT_LINE('Total Business Days : '||TO_CHAR(BusinessDays) ) ;
  6* END;
SQL> /
Total Business Days : 23

pnielsen >> Det ser rigtig, rigtig fornuftigt ud. Nu er jeg ikke vant til at arbejde med procedurer eller i PL/SQL, men arbejder udelukkende med SQL/Oracle i webapplikationer.. kan du lede mig på rette vej..? Hvis ikke, tror jeg nok jeg måske alligevel kan bygge noget på det du har skrevet..

Jeg kan se en fejl i syntaksen:
CREATE OR FUNCTION...
skal være
CREATE OR REPLACE FUNCTION...

Når man først har oprettet sin funktion ligger den gemt i databasen og kan kaldes på et vilkårligt tidspunkt.

Såvel som man kan skrive:
SELECT 2+3 AS sum FROM DUAL;
kan man nu skrive
SELECT TotWeekDays('01-MAY-2001','31-MAY-2001') AS ugedage FROM DUAL;

Jeg har nu oprettet funktionen i databasen, men når jeg kalder den med SELECT TotWeekDays('01-MAY-2001','31-MAY-2001') AS ugedage FROM DUAL; får jeg:

The following error has occurred:

ORA-01858: a non-numeric character was found where a numeric was expected
ORA-06512: at line 1

Hej Nil,

Det kunne tyde på at du har en anden NLS_DATE_FORMAT i din session/dit setup.
Du kan se hvad den står til med
show parameter NLS_DATA_FORMAT
Hvis der ikke står noget i den, kommer det an på din NLS_LANGUAGE eller NLS_TERRITORY  - som i mit tilfælde står til AMERICAN.


Ret det i sessionen med følgende - og det vil virke .. tror jeg :-)
SQL> alter session set NLS_DATE_FORMAT='DD-MON-YYYY';

Session altered.

SQL>  SELECT TotWeekDays('01-MAY-2001','31-MAY-2001') AS ugedage FROM DUAL;

  UGEDAGE
----------
        23

SQL>

Du kan til en hver tid se, hvordan dit datoformat ser ud ved at skrive:
SELECT SYSDATE FROM DUAL;

Som det fremgår af definitionen tager funktionen TotWeekDays to datotid-felter som input. Du skal bare være sikker på, at de værdier du indtaster bliver tolket som en korrekt dato.

Hvis man vil være helt uafhængig af datoformat kan man anvende TO_DATE, hvor man selv angiver det angivne datoformat. Dette eksempel burde fungere uanset datoformat:
SELECT TotWeekDays( TO_DATE('2001-05-01', 'YYYY-MM-DD'), TO_DATE('2001-05-01', 'YYYY-MM-DD') ) AS ugedage FROM DUAL;

hov!
... angiver det ØNSKEDE datoformat.

I er begge en stor hjælp. Jeg har prøvet begge jeres forslag og begge giver:

The following error has occurred:

ORA-01846: not a valid day of the week
ORA-06512: at "NIGTEKNIK.TOTWEEKDAYS", line 4
ORA-06512: at line 1




Details:
ORA-01846: not a valid day of the week
ORA-06512: at "NIGTEKNIK.TOTWEEKDAYS", line 4
ORA-06512: at line 1

Det er nok strengene 'SUNDAY' og 'SATURDAY', som ikke er lovlige strenge med dit aktuelle dato/language-format.
- Prøver at finde en løsning...

execute dbms_session.set_nls('NLS_DATE_LANGUAGE','AMERICAN');

pnielsen >> Jeg er lidt usikker på at begynde at ændre databasens indstillinger når jeg ikke er DBA. Kan det ikke påvirke andre applikationer?

Det betyder intet i dette tilfælde. Du sætter det kun i den session du kører statementet.

Problemet er at next_day kigger forventer dine dage på engelsk - måske det endda kan lade sig gøre med dine aktuelle settings. Prøv den ovenstående først....

Laver et hack:
Dette grimme udtryk giver altid lørdag skrevet i det aktuelle sprog
TO_CHAR(TO_DATE('2002-01-05','YYYY-MM-DD'),'DAY')
og søndag
TO_CHAR(TO_DATE('2002-01-06','YYYY-MM-DD'),'DAY')

Dem sætter vi ind i den oprindelige funktion i stedet for 'SATURDAY' og 'SUNDAY':
CREATE OR REPLACE FUNCTION TotWeekDays(FromDate DATE, ToDate  DATE)
RETURN NUMBER IS
  ToTalSunDays NUMBER;
  ToTalSaturDays NUMBER;
  CharLoerdag VARCHAR2(10) := TO_CHAR(TO_DATE('2002-01-05','YYYY-MM-DD'),'DAY');
  CharSoendag VARCHAR2(10) := TO_CHAR(TO_DATE('2002-01-06','YYYY-MM-DD'),'DAY');
BEGIN
  ToTalSunDays  := NEXT_DAY(ToDate - 7, CharSoendag) -
                  NEXT_DAY(FromDate - 1, CharSoendag);

  ToTalSaturDays := NEXT_DAY(ToDate - 7, CharLoerdag) -
                    NEXT_DAY(FromDate - 1, CharLoerdag);

  RETURN (ToDate - FromDate - (TotalSundays+TotalSaturdays)/7 -1) ;
END;
/

Gå ikke glip af denne tråd:
http://www.eksperten.dk/spm/273517

Lasse_buck >>Jeg vil anbefale at sætte det med dbms_session.set_nls. Så er du sikker på at du altid vil have det samme enviroment. Ovenstående "hack" virker se'fø'li, men der vil være alt for mange mulighed - og derved endnu flere fejlmuligheder... Hvordan ser det ud på polsk og finsk etc...
Bare dårlig erfaring... men det virker i dette tilfælde!

Tak begge to. Nu har jeg fået det til at virke. Jeg vil gerne dele pointene mellem jer, da jeg mener at I har hjulpet mig ca lige meget.

Hav en god dag.

Takker for point!

Min løsning er måske ikke pæn, men den er IKKE sårbar overfor forskellige nls settings, da min TO_DATE ALTID danner en valid lørdag og søndag i DATE format.

DATE er universel under alle formater, men det er konvertering til og fra strenge, som giver problemer.

SELECT TotWeekDays(sysdate, sysdate + 14) FROM DUAL;
vil altid give det rigtige resultat med min løsning, men skriver man i stedet
SELECT TotWeekDays(datoStrengFra, datoStrengTil) FROM DUAL;
kræver det, at input-datoerne passer til det aktuelle datoformat.

Tja.. min erfaring siger at TO_CHAR(TO_DATE... nemt giver problemer.

Good luck

lige et lille tillægsspm... jeg kan ikke helt gennemskue hvordan jeg trækker antallet af lørdag i måneden ud..

Vil gerne smide flere points i hvis nogen hurtigt kan hjælpe mig..

http://www.eksperten.dk/spm/276611