Changing the first day of the week
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Changing the first day of the week

  1. #1
    Join Date
    Sep 2005
    Posts
    46

    Changing the first day of the week

    hai every body,

    to_char(sysdate, 'd') returns the day of week like 1 thru 7 for sundays thru saturday. Here the first day of week is Sunday i.e., 1 -> sunday. But in my application first day of week is Monday. Is it possible to change the first day of week in oracle so that to_char(sysdate, 'd') still returns the day of week from 1 thru 7 for Monday thru Sunday. i.e., 1 -> Monday.

    Thanking you in advance,


    Regards
    Issac

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    change your client NLS settings to that of what you need, for example
    Code:
    [oracle@xxx oracle]$ export NLS_LANG='AMERICAN';
    [oracle@xxx oracle]$ sqlplus "/ as sysdba"
    
    SQL*Plus: Release 9.2.0.5.0 - Production on Mon Oct 31 09:32:55 2005
    
    Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
    
    
    Connected to:
    Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
    With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining options
    JServer Release 9.2.0.5.0 - Production
    
    SQL> select to_char(sysdate, 'D') from dual;
    
    T
    -
    2
    
    SQL> Disconnected from Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
    With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining options
    JServer Release 9.2.0.5.0 - Production
    [oracle@xxx oracle]$ export NLS_LANG=ENGLISH
    [oracle@xxx oracle]$ sqlplus "/ as sysdba"
    
    SQL*Plus: Release 9.2.0.5.0 - Production on Mon Oct 31 09:33:19 2005
    
    Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
    
    
    Connected to:
    Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
    With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining options
    JServer Release 9.2.0.5.0 - Production
    
    SQL> select to_char(sysdate, 'D') from dual;
    
    T
    -
    1

  3. #3
    Join Date
    Sep 2005
    Posts
    46
    That was gr8 indeed..
    thanks davey23uk for your help..

    But this means i'll have to do this for all sessions. Is there any such parameter that can be toggled so that its like that through out the life of the database.

    And is there any way that I can view the value of this nls_lang paramater.

    Thanking you for your precious time . ..

    Regards
    Issac

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    you just need to change NLS_LANG environment (registry setting) variable on each client PC

  5. #5
    Join Date
    Sep 2005
    Posts
    46
    If you don't mind..

    Can you please explain how to do that...

    Regards
    Saneesh

  6. #6
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    do you know how to set environment variables?

  7. #7
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Yo don't need to change NLS_LANG env variables (btw, it is TERRITORY part that influences the first day of week, not the LANGUAGE part) on all your clients PCs if you can't or don't want to, it is sufficent to change the NLS_TERRITORY for the session where you want to change the ordering of the days in a week.

    The following example will do this just fine:

    ALTER SESSION SET NLS_TERRITORY = 'UNITED KINGDOM';

    You can include something like that inside your application immediately after users log on, or you can create a database logon trigger and change NLS_TERRITORY in it for the sessions in which you want this to happen automaticaly.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

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


Click Here to Expand Forum to Full Width