SQL Query where week starts first day of year.
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: SQL Query where week starts first day of year.

  1. #1
    Join Date
    Mar 2004
    Posts
    55

    SQL Query where week starts first day of year.

    I have a query which does a count of widgets produced a week over a period of time.

    However my 1st week of the year starts 1 Jan 2005 and end 2 Jan 2005.

    The 2nd week would be 03 Jan 2005 and end 09 Jan 2005.



    I know oracle has rules in terms of how it handles the first week number using IW and WW. However I am at pains create a script or mask that would follow the rule i have described above.

    Advice appreciated.

  2. #2
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    OOOPS - bad post.
    Last edited by DaPi; 01-31-2005 at 03:40 AM.

  3. #3
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    However my 1st week of the year starts 1 Jan 2005 and end 2 Jan 2005.
    ===
    Where do you live? I mean the country name.

    Tamil

  4. #4
    Join Date
    Mar 2004
    Posts
    55
    Not sure what the issue is here since i am asking a simple question. Two replies have said nothing.

    I am sorry if it may sould stupid to the oracle gods.

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Since you don't want to answer to the very valid tamil's question about your NLS_TERRITORY setting, I'll assume you are in one of those territories where week starts on MONDAY. (If according to your NLS settings your week starts on SUNDAY, you'll have to modify the following function accordingly):
    Code:
    CREATE OR REPLACE FUNCTION my_non_iso_week (p_date DATE)
      RETURN INTEGER
    IS
      l_first_day CHAR(1);
      l_result NUMBER(2);
    BEGIN
      l_first_day := TO_CHAR(TRUNC(p_date, 'YYYY'), 'D');
      IF TO_CHAR(p_date, 'D') >= l_first_day THEN
        l_result := TO_NUMBER(TO_CHAR(p_date, 'WW'));
      ELSE
        l_result := TO_NUMBER(TO_CHAR(p_date, 'WW'))+1;
      END IF;
      RETURN l_result;
    END;
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  6. #6
    Join Date
    Mar 2004
    Posts
    55
    Ok my apologies. My location is Australia

  7. #7
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Ok, so I'm sure you'll know what to change in the above function...
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  8. #8
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Thanks Modic.

    Mozart,

    If you had clearly posted your requirement, many people would have given the answers.

    If the first week contains only 2 days (Jan 1, 2005 and Jan 2, 2005 only), your weekly scheduling algorithm (I assume) program may not work correctly. You need to code much more than if you use standard ISO week.

    Tamil

Posting Permissions

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



Click Here to Expand Forum to Full Width