Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2003
    Location
    Nottingham, England
    Posts
    52

    Unanswered: Getting the week number for a given date

    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

  2. #2
    Join Date
    Mar 2007
    Posts
    623
    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):
    Datetime Format Elements

    Element
    Description


    WW
    Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year.

    IW
    Week of year (1-52 or 1-53) based on the ISO standard.
    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.

  3. #3
    Join Date
    Dec 2003
    Location
    Nottingham, England
    Posts
    52
    Thanks for the reply.

    For info, whilst working my way through the myriad of migration issues Im having I have been reading the Oracle docs, so much in fact that its 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 doesnt 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?

  4. #4
    Join Date
    Mar 2007
    Posts
    623
    What do you think?
    You are making things unnecessarily difficult. Why not simply
    Code:
    correct_week := to_char(vt_datetime + 1, 'iw');
    ? 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.

  5. #5
    Join Date
    Dec 2003
    Location
    Nottingham, England
    Posts
    52
    Yes, ofcourse, I can just +1 onto all dates when retreiving the correct week number.

    Thanks...

Posting Permissions

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