Since 08-NOV-09 10.00000 PM, is a Sunday, I would like to see a new WEEK_NUM associated to it. SELECT EVENT_TIME, to_char(EVENT_TIME, 'iw') WEEK_NUM NLS_RDBMS_VERSION-10.2.0.3.0Įxample of the issue I am trying to resolve: NLS_TIMESTAMP_TZ_FORMAT-DD-MON-RR HH.MI.SSXFF AM TZR NLS_TIMESTAMP_FORMAT-DD-MON-RR HH.MI.SSXFF AM Given below is my NLS_DATABASE_PARAMETERS: I would like to change the beginning of week in Oracle from Monday to Sunday. 1.7K Training / Learning / Certification.165.3K Java EE (Java Enterprise Edition).7.9K Oracle Database Express Edition (XE).
How to get the week number depends on how you define the week number.
The 'magic number' 2 in the expression above reflects the fact that you week starts 2 days before the ISO week, which starts on Monday.
If the date is (Sunday) I'm getting week No. 51 which conforms with our working calender. I converted date to week number using the following :TO_NUMBER(TO_CHAR(, ' iw')). Subject: Setting Sunday as first day of the week Http:/ / From: dovluft via oracle-sql-l [mailto: Thursday, Octo9:32 AM There is a discussion about the NLS_TERRITORY setting and the first day of the week in the Globalization Support Guide that seems directly on point here.
I don't know off the top of my head, though, what territories use Sunday as the first day of the week. Return in your session? If that does not return 51, you probably need to change your NLS_TERRITORY.
The week, however, derives from the NLS_TERRITORY. What country does this convention derive from? What is your session's NLS_TERRITORY?