Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
PL/SQL Release 11.1.0.7.0 - Production
CORE 11.1.0.7.0 Production
TNS for 64-bit Windows: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production

Hello all.

I am currently migrating our product from SQL SERVER to ORACLE and have the following issue.
Basically I'm just trying to get the year week number for a given date but I'm having trouble with Oracle as it seems to think that the weeks run from Thursday to Thursday?. I presume this is something to do with the fact that the first day of the year was Thursday?
e.g.

SQL SERVER:
select DATEPART(wk, '2009-10-24') as Sat -- 43 - correct
select DATEPART(wk, '2009-10-25') as Sun -- 44 - correct
select DATEPART(wk, '2009-10-26') as Mon -- 44 - correct
select DATEPART(wk, '2009-10-27') as Tue -- 44 - correct
select DATEPART(wk, '2009-10-28') as Wed -- 44 - correct
select DATEPART(wk, '2009-10-29') as Thu -- 44 - correct
select DATEPART(wk, '2009-10-30') as Fri -- 44 - correct
select DATEPART(wk, '2009-10-31') as Sat -- 44 - correct
select DATEPART(wk, '2009-11-1') as Sun -- 45 - correct
select DATEPART(wk, '2009-11-2') as Mon -- 45 - correct
select DATEPART(wk, '2009-11-3') as Tue -- 45 - correct
select DATEPART(wk, '2009-11-4') as Wed -- 45 - correct
select DATEPART(wk, '2009-11-5') as Thu -- 45 - correct

ORACLE:
SELECT to_char(to_date('24-OCT-2009'), 'ww') as Sat from dual; -- 43 correct
SELECT to_char(to_date('25-OCT-2009'), 'ww') as Sun from dual; -- 43 incorrect - should be 44
SELECT to_char(to_date('26-OCT-2009'), 'ww') as Mon from dual; -- 43 incorrect - should be 44
SELECT to_char(to_date('27-OCT-2009'), 'ww') as Tue from dual; -- 43 incorrect - should be 44
SELECT to_char(to_date('28-OCT-2009'), 'ww') as Wed from dual; -- 43 incorrect - should be 44
SELECT to_char(to_date('29-OCT-2009'), 'ww') as Thu from dual; -- 44 correct
SELECT to_char(to_date('30-OCT-2009'), 'ww') as Fri from dual; -- 44 correct
SELECT to_char(to_date('31-OCT-2009'), 'ww') as Sat from dual; -- 44 correct
SELECT to_char(to_date('1-NOV-2009'), 'ww') as Sun from dual; -- 44 incorrect - should be 45
SELECT to_char(to_date('2-NOV-2009'), 'ww') as Mon from dual; -- 44 incorrect - should be 45
SELECT to_char(to_date('3-NOV-2009'), 'ww') as Tue from dual; -- 44 incorrect - should be 45
SELECT to_char(to_date('4-NOV-2009'), 'ww') as Wed from dual; -- 44 incorrect - should be 45
SELECT to_char(to_date('5-NOV-2009'), 'ww') as Thu from dual; -- 45 correct

Now I don't want to get into a discussion with regard to locales etc - in my world (and it seems SQL SERVER's) the first day of the week is Sunday and the last Saturday.

Is there some NLS_? setting or something that I'm missing?

thanks for any help on this.

Andy

Hello,
firstly please get familiar with Oracle documentation, as it contains a lot of useful information. It is available e.g. online on http://tahiti.oracle.com/.

In SQL Reference book, you would find this info (located here fror 11gR1): ISO week starts always on Monday regardless any settings. If you want the week to start on different day, you have to adjust it. For Sunday, adding one day (+ INTERVAL '1' DAY or simply + 1) to the source date shall do the job.

Thanks for the reply.

For info, whilst working my way through the myriad of migration issues I’m having I have been reading the Oracle docs, so much in fact that it’s not uncommon for my eyes to be bleeding by the end of the day.
It's just nice to get some helpful advice every once in a while to prove to yourself that the world isn't such a bad place...

Saying that though I am now going to unashamedly ask for some more help – and I admit that I have only spent a hour or so looking into this, this very morning.

My solution to this problem can be solved with the below SQL (vendortest.vt_datetime is a TIMESTAMP column)

SELECT
to_char(vt_datetime, 'Day') AS day,
to_char(vt_datetime, 'Month') AS monthname,
to_char(vt_datetime, 'MM') AS month,
case when to_char(vt_datetime, 'D') = '7'
then
to_char(vt_datetime + 1, 'iw')
else
to_char(vt_datetime, 'iw')
end as week,
to_char(vt_datetime, 'yyyy') AS year,
to_char(vt_datetime, 'q') AS quarter
from vendortest;

And so for the dates:
24-OCT-09 00.00.00.000000000 (Sat)
25-OCT-09 00.00.00.000000000 (Sun)
26-OCT-09 00.00.00.000000000 (Mon)
I get :

DAY MONTHNAME MONTH WEEK YEAR QUARTER
--------- --------- ----- ---- ---- -------
Saturday October 10 43 2009 4
Sunday October 10 44 2009 4
Monday October 10 44 2009 4

What I was thinking though is that it would be nice to have a custom SQL BIF to do this.

Something along the lines of: (Note this doesn’t work and the syntax is probably a load of b****cks)

create or replace function
year_week_number(p_date)
return TIMESTAMP DETERMINISTIC
AS
correct_week TIMESTAMP
BEGIN
correct_week :=
select case when to_char(vt_datetime, 'D') = '7'
then
to_char(vt_datetime + 1, 'iw')
else
to_char(vt_datetime, 'iw')
end from dual;
return correct_week;
END
END;

I could then call this along the lines of:
select year_week_number(to_date('25-OCT-2009')) from dual

What do you think?

You are making things unnecessarily difficult. Why not simply ? For every day except Sunday, the following day (VT_DATETIME +1) belongs to the same ISO week as the day. So you may use the following day for every day in week.

By the way, the correct assignment from SQL query in PL/SQL is by using SELECT INTO statement. You may use CASE statement directly; alternatively IF THEN ELSE statement is available too.
All these statements (with correct syntax) are described in PL/SQL User's Guide and Reference.

Yes, ofcourse, I can just +1 onto all dates when retreiving the correct week number.

Thanks...