Difference between Oracle's date formatted as "ww" and Access' formatted as "ww"?

Posted on 2009-02-09
Last Modified: 2013-12-18
I have a query in which I convert a date to it's week of the year "ww".  When I am writting the code to run directly on the Oracle Tables I use:

To_Char(To_Date("Some_Date",'MM/DD/YYYY'),'WW') as WkOfYear

In Access I am doing this:
Format(qryTemp_Data!Some_Date,"ww") AS WkOfYear

What I have found is that for the 4th of February, Oracle says it is the 5th week, but Access says it is the 6th.  I know that the 5th of February would actually be the true 6th week, and Oracle changes to the 6th week on that day, but I am curious what each looks at and is there a way to make it so that the full "Work Week" is the same in Oracle and not split up?
Question by:rgn2121
    LVL 27

    Accepted Solution

    Probably access is giving ou the week of the year based on the ISO standard, where a week always begin on monday.
    You can get that result in oracle by the following query.

    SQL> select to_char(to_date('20090204','yyyymmdd'), 'Iw') from dual;


    The 'WW' format in oracle counts the week numbers from the first day of the year.

    SQL> select to_char(to_date('20090204','yyyymmdd'), 'ww') from dual;

    LVL 22

    Assisted Solution

    by:Ivo Stoykov
    this is because of the standard used
    to achieve same result as in access you should use another formatter - ISO - then  4th feb is in 6th week

    select To_Char(To_Date('02/04/2009','MM/DD/YYYY'),'IW') as WkOfYear from dual

    Open in new window

    LVL 12

    Author Comment

    I tried this and it is giving me an error...ORA-01858 - Non Numeric Character
    To_Char(To_Date("Some_Date",'yyyymmdd'),'lw') as FLT_WEEK

    Some date is one of the fields of an inner query....
    LVL 12

    Author Comment

    by:rgn2121 was an " i " and not an " L "...That was the issue...  Thanks
    LVL 12

    Author Closing Comment

    I gave most of the points to sujith80 since he had the right solution, I just couldn't tell if it was an " i " or an " L ".  Thanks to both!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
    In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
    This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
    Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now