SQL Query where week starts first day of year.

[mozart]

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.

[DaPi]

OOOPS - bad post.

[tamilselvan]

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

[mozart]

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.

[jmodic]

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):

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;

[mozart]

Ok my apologies. My location is Australia

[jmodic]

Ok, so I'm sure you'll know what to change in the above function...

[tamilselvan]

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