?
Solved

What Day is Today

Posted on 2009-04-08
9
Medium Priority
?
883 Views
Last Modified: 2013-12-19
Hey Folks,

I was wondering if you could help?  Needless to say I was driven crazy for a week before I whittled the problem down to the fact that our databases are set to "AMERICA" when we are in "AUSTRALIA":

select * from sys.props$ where name = 'NLS_TERRITORY'
gives...
NAME    VALUE$    COMMENT$
NLS_TERRITORY    AMERICA    Territory

The question is:
Why do different Oracle clients give me a different answer to...?
select sysdate, to_char(sysdate, 'D') day_id from dual

If Today is Thursday, the answer to this SELECT is different with different VB.NET, Oracle clients (11g ODP.NET / 9i OracleClient)...

Oracle 9i design time (running query as "Preview Data" in a strongly-typed dataset)
9/04/2009 1:59 PM      5

Oracle 9i runtime:
9/04/2009 2:00 PM      4 (correct as far as I'm concerned!!!)

Oracle 11g (design / runtime):
9/04/2009 2:01 PM      5

I would like to be able to run either client and always get an answer of "4" for a Thursday.

Any help would be most appreciated.

cheers,
SnowyIT

0
Comment
Question by:hydroIT
  • 3
  • 3
  • 3
9 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24104073
try this:

select sysdate, to_char(sysdate, 'D', NLS_TERRITORY 'AMERICA') day_id from dual

Open in new window

0
 

Author Comment

by:hydroIT
ID: 24104336
Hmmm, thanks Angellll, looking good...

However, is that the exact syntax?  It gives the error:

ORA-00907: missing right parenthesis

BTW Looking in:
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\

I noticed my home, which is 9i (\HOME0), has NLS_LANG key = "ENGLISH_AUSTRALIA.WE8MSWIN1252"
whereas 11g (\KEY_ora11g) has NLS_LANG = "AMERICAN_AMERICA.WE8MSWIN1252"

whereas

Cheers


0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24104608
sorry, small typo:
http://www.ss64.com/orasyntax/nls.html

select sysdate, to_char(sysdate, 'D', 'NLS_TERRITORY=AMERICA') day_id from dual

Open in new window

0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
LVL 18

Expert Comment

by:sventhan
ID: 24107151
try this...
select sysdate, to_char(sysdate, 'DAY') TODAY_IS from dual  
0
 

Author Comment

by:hydroIT
ID: 24134482
Thanks guys,

Angellll, your updated sql still seems to upset Oracle:
"select sysdate, to_char(sysdate, 'D', 'NLS_TERRITORY=AMERICA') day_id from dual"
gives
ORA-12702: invalid NLS parameter string used in SQL function

and Sventhan, using 'DAY' as a format gives "TUESDAY" for instance but I was hoping on getting the Day of Week index (ie "2") as that is used in referenced tables as a foreign key.

Any other ides?
Many thanks
HydroIT
0
 
LVL 18

Expert Comment

by:sventhan
ID: 24136605
try this...

select sysdate, to_char(sysdate, 'D') TODAY_IS from dual  
http://www.techonthenet.com/oracle/functions/to_char.php 
0
 

Accepted Solution

by:
hydroIT earned 0 total points
ID: 24144141
Hey Sventhan,

Thanks for the suggestion, but that's the sql that has been not playing ball from the start.  It is dependent on you "NLS_LANG" registry property of your client locale.

I think I have finally found the answer on another thread:
http://forums.oracle.com/forums/thread.jspa?messageID=2022888

select mod(to_char(sysdate, 'J'), 7) + 1 from DUAL

Cheers for your efforts,
HydroIT
0
 
LVL 18

Expert Comment

by:sventhan
ID: 24144376
sooper!!!
I'm glad you found the solution.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24158045
sorry for coming back "late". this should do also, simpler, without having to rely on the current client's nls_xxx settings:

TO_CHAR(sysdate, 'D', 'nls_date_language = American')

Open in new window

0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to recover a database from a user managed backup
Suggested Courses

593 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