Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2003
    Posts
    26

    Unanswered: select entries from whole week

    Hello,

    I'd like to get all entries from "the whole week" (begin: Monday end: Sunday)
    from e.g the day sysdate - 222 (today: 20.10.2004) .This means all entries with a date between 08.03.2004
    and 14.03.2004 (including the borders !)

  2. #2
    Join Date
    Apr 2004
    Posts
    246
    where date_col between next_day(trunc(sysdate)-222-7,'mon') and
    next_day(trunc(sysdate)-222-7,'mon') + 7 - 1/24/60/60
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

  3. #3
    Join Date
    Aug 2003
    Posts
    26
    @shoblock: Cool, thanks a lot.

    Maybe one question: I use a stored procedure

    Code:
    PROCEDURE time_menu (Resultset IN OUT time_menu_CURSOR 
    ,DATE_OFFSET NUMBER, DATE_CURRENT DATE ,SWITCH VARCHAR2)
    ...
    date_start VARCHAR2(16);
    date_stop VARCHAR2(16);
    ...
    
    --- today
    IF ( UPPER(SWITCH) = 'TODAY' ) THEN
    
    date_start := TO_CHAR(SYSDATE-DATE_OFFSET, 'dd.mm.yyyy');
    date_stop  := TO_CHAR(SYSDATE-DATE_OFFSET+1, 'dd.mm.yyyy');
    
    
    --- yesterday
    ELSIF  ( UPPER(SWITCH) = 'YETSERDAY' ) THEN
    date_start := TO_CHAR(SYSDATE-DATE_OFFSET-1, 'dd.mm.yyyy');
    date_stop  := TO_CHAR(SYSDATE-DATE_OFFSET, 'dd.mm.yyyy');
    
    --- whole week 
    ELSIF  ( UPPER(SWITCH) = 'WHOLE WEEK' ) THEN
    --THE_TIME between next_day(trunc(sysdate)-222-7,'mon') and
    --next_day(trunc(sysdate)-222-7,'mon') + 7 - 1/24/60/60
    
    ---   PROBLEM --------------
    date_start := TO_CHAR( NEXT_DAY(TRUNC(SYSDATE)-DATE_OFFSET-7, 'mon')  );
    date_stop  := TO_CHAR( NEXT_DAY(TRUNC(SYSDATE)-DATE_OFFSET-7, 'mon') +7 - 1/24/60/60    );
    ---   PROBLEM --------------
    END IF;
    
    SELECT ROWNUM ,THE_TIME FROM MYTABLE
    
    WHERE
    
    THE_TIME BETWEEN
    NVL( TO_DATE(date_start,'dd.mm.yyyy hh24:mi') ,THE_TIME)
    AND NVL( TO_DATE(date_stop,'dd.mm.yyyy hh24:mi') ,THE_TIME);
     			
    ...
    I get a date_offset and try the select all entries from "today regarding this date", "yesterday regarding this
    date", "whole week regarding this date",
    and later "whole week forward/backward regarding this date" --> therefore the input parameter DATE_CURRENT

    Now I have the problem of implementing the "whole week " solution suggested by shoblock in the procedure.

    Please help.

  4. #4
    Join Date
    Apr 2004
    Posts
    246
    Don't understand what the "problem of implementing the whole week" is. It appears to in your code. BTW, don't store dates as varchars. You calc a date, convert to char, then convert back to date later - this makes no sense.

    date_start date;
    date_stop date;
    ...
    date_start := SYSDATE-DATE_OFFSET;
    ...
    WHERE THE_TIME BETWEEN NVL( date_start, THE_TIME) ...
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

  5. #5
    Join Date
    Dec 2003
    Posts
    76

    Week Interval problem ...

    Hi.

    I have a problem working with dates. Its like this:
    1 - Imagine I have an date Interval (i.e. 01-01-2004 and 31-05-2004);
    2 - Imagine I need to know the Week Interval between those 2 weeks;
    3 - Imagine that I use the next statement to access week number, in ORACLE: TO_CHAR(TO_DATE(date_field, 'dd-mm-yyyy'), 'IW'));
    4 - Imagine that I need the Week Interval in ONLY ONE Select;


    Is there any way to retrieve that Info from one single Select to the DUAL table, for instance ???

    Thanx,

  6. #6
    Join Date
    Jul 2003
    Posts
    2,296
    Code:
      1* select TO_CHAR(TO_DATE('31-05-2004', 'dd-mm-yyyy'), 'IW') from dual
    SQL > /
    
    TO
    --
    23
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  7. #7
    Join Date
    Apr 2004
    Posts
    246
    do mean the number of weeks between the two dates? oracle has date math.

    declare
    date_start date := to_date('01-01-2004' ,'DD-MM-YYYY');
    date_stop date := to_date('31-05-2004' ,'DD-MM-YYYY');
    begin
    dbms_output.put_line( date_stop - date_start ); -- returns # of days (hours are the decimal part)
    dbms_output.put_line( (date_stop-date_start)/7 ); -- # of weeks
    end;
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

  8. #8
    Join Date
    Dec 2003
    Posts
    76
    Thankx for the help ...
    It is not exactly what I need, but it suites me ...

    Thankx,

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •