Hi there,
How could you get the day of the week in number, regardless what the session is !
There is a function, to_char, with a formating, 'D', which returns the day of week, but sunday is 7 in europe and 1 in america.
Could anyone figure out how I could pass the nls territory to the to_char function??? I am not willing to change the session parameter !
SQL> select to_char(sysdate, 'Day "=" D') from dual;
TO_CHAR(SYSDA
-------------
Tuesday = 2
SQL> alter session set nls_territory='AMERICA';
Session altered.
SQL> select to_char(sysdate, 'Day "=" D') from dual;
TO_CHAR(SYSDA
-------------
Tuesday = 3
SQL> select to_char(sysdate, 'Day "=" D', 'NLS_TERRITORY=''america''') from dual;
select to_char(sysdate, 'Day "=" D', 'NLS_TERRITORY=''america''') from dual
*
ERROR at line 1:
ORA-12702: invalid NLS parameter string used in SQL function
Note that this NLS dependent function could be used in a materialized view, making the mview nls-dependent, which is against the guideline of mviews (The defining query of the materialized view cannot contain any non-repeatable expressions, http://download-west.oracle.com/docs/cd/B13789_01/server.101/b10736/basicmv.htm#sthref483)
Good luck !
Laurent
Regards
Laurent