[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2113
  • Last Modified:

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

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?
0
rgn2121
Asked:
rgn2121
  • 3
2 Solutions
 
sujith80Commented:
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;

TO
--
06

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;

TO
--
05
0
 
Ivo StoykovCommented:
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
HTH
i



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

Open in new window

0
 
rgn2121Author Commented:
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....
0
 
rgn2121Author Commented:
Okay...it was an " i " and not an " L "...That was the issue...  Thanks
0
 
rgn2121Author Commented:
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!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now