Tom,

The default format for Oracle is DD-MON-RR for Oracle 8i
SQL>select sysdate from x$dual;
06-JUN-02
SQL>set time on
11:39:30 SQL>

When I try to do
SQL>recover database until time '30-MAY-02 11:00:00' ;
I ger ORA-00351

However when I subsequently do

SQL>alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';
SQL>recover database until time '2002-05-30 11:00:00';
It is successful

How to find what does Oracle use for nls_format when doing recovery

Thanks 

SQL> alter session set nls_date_format = 'mm/dd/yyyy hh24:mi:ss';

SQL> recover database until time '05/24/2002 10:49:30' using 
     backup controlfile;
ORA-01843: not a valid month

The following format works although not the documented format:
--------------------------------------------------------------
SQL> alter session set nls_date_format = 'mon/dd/yyyy hh24:mi:ss';

Session altered.

SQL> recover database until time 'may/29/2002 17:04:23' 
     using backup controlfile;

recovery complete
 

No need to edit the registry. All you need is to create a System variable NLS_DATE_FORMAT in your Windows environment.

For Windows2000, Right click on "My Computer" icon and click on properties. Then click on Advanced tab and then click "Environment Variables" to create NLS_DATE_FORMAT env. variable.

See the following example to see the effect of changing the environment variable inside the MS-DOS session:

=========================================================
C:\>echo %NLS_DATE_FORMAT%
DD-MON-YYYY

C:\>sqlplus scott/tiger

SQL*Plus: Release 9.2.0.1.0 - Production on Sun Apr 20 22:05:05 2003

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

SQL> select sysdate from dual ;

SYSDATE
-----------
20-APR-2003

SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

C:\>set NLS_DATE_FORMAT=YYYY-MM-DD

C:\>sqlplus scott/tiger

SQL*Plus: Release 9.2.0.1.0 - Production on Sun Apr 20 22:05:27 2003

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

SQL> select sysdate from dual ;

SYSDATE
----------
2003-04-20

SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

C:\>
=========================================================
 

While we are comparing our entry date field with sysdate for counting no. of records, it is not giving proper results.

Details is given below...
SQL> alter session set nls_date_format='dd/mm/rrrr'
  2  ;

Session altered.

SQL> create table dttest
  2  (empno number(4),
  3  entrydate date);

Table created.

SQL> insert into table(1,sysdate);
insert into table(1,sysdate)
            *
ERROR at line 1:
ORA-00903: invalid table name 


SQL> insert into dttest values(1,sysdate);

1 row created.

SQL> insert into dttest values(2,sysdate);

1 row created.

SQL> insert into dttest values(3,sysdate);

1 row created.

SQL> select count(*) from dttest
  2  where entrydate=sysdate;

 COUNT(*)                                                                                           
---------                                                                                           
        0                                                                                           

SQL> select count(*) from dttest
  2  where to_char(entrydate,'dd/mm/rrrr')='13/08/2004';

 COUNT(*)                                                                                           
---------                                                                                           
        3                                                                                           

SQL> spool off

Does it mean that every time when we need to count, we have to convert into char ?


 

Hi Tom, 

Take a look at the following.

Why is this new Data type behaving strangely ?


My aim is to get the same functionality as that offered by add_months ,but also i want to be able to add specific Seconds/Minutes/Days .


SQL> alter session set nls_date_format = 'DD.MM.YYYY HH24:MI:SS' ;

Session wurde geändert.

SQL> SELECT LAST_DAY(  SYSDATE  ) + INTERVAL  '0000-01'   YEAR (4) TO MONTH    next_mon1 from dual ;

SELECT LAST_DAY(  SYSDATE  ) + INTERVAL  '0000-01'   YEAR (4) TO MONTH    next_mon1 from dual
                             *
FEHLER in Zeile 1:
ORA-01839: date not valid for month specified



SQL> SELECT   ADD_MONTHS(  LAST_DAY(SYSDATE) , 1 )  next_mon2 from dual ;

NEXT_MON2
-------------------
28.02.2005 11:30:23


SQL> SELECT LAST_DAY(  SYSDATE  )      +    to_yminterval('0000-01')     next_mon1 from dual ;
SELECT LAST_DAY(  SYSDATE  )      +    to_yminterval('0000-01')     next_mon1 from dual
                                  *
FEHLER in Zeile 1:
ORA-01839: date not valid for month specified 

Hi Tom,

Even if I change "nls_date_format" to something else like 'DD-MON-YYYY HH24:MI:SS', the nls_database_parameters still shows me the same old value, how can I see what is in effect at current time.

SQL> select * from nls_database_parameters;

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE                   AMERICAN
NLS_TERRITORY                  AMERICA
NLS_CURRENCY                   $
NLS_ISO_CURRENCY               AMERICA
NLS_NUMERIC_CHARACTERS         .,
NLS_CHARACTERSET               WE8ISO8859P1
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                DD-MON-RR
NLS_DATE_LANGUAGE              AMERICAN
NLS_SORT                       BINARY
NLS_TIME_FORMAT                HH.MI.SSXFF AM

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZH:TZM
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZH:TZM
NLS_DUAL_CURRENCY              $
NLS_COMP                       BINARY
NLS_NCHAR_CHARACTERSET         WE8ISO8859P1
NLS_RDBMS_VERSION              8.1.7.0.0

18 rows selected.

SQL> 
SQL> 
SQL> 
SQL> 
SQL> 
SQL> alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';

Session altered.

SQL> select * from nls_database_parameters;

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE                   AMERICAN
NLS_TERRITORY                  AMERICA
NLS_CURRENCY                   $
NLS_ISO_CURRENCY               AMERICA
NLS_NUMERIC_CHARACTERS         .,
NLS_CHARACTERSET               WE8ISO8859P1
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                DD-MON-RR
NLS_DATE_LANGUAGE              AMERICAN
NLS_SORT                       BINARY
NLS_TIME_FORMAT                HH.MI.SSXFF AM

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZH:TZM
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZH:TZM
NLS_DUAL_CURRENCY              $
NLS_COMP                       BINARY
NLS_NCHAR_CHARACTERSET         WE8ISO8859P1
NLS_RDBMS_VERSION              8.1.7.0.0

18 rows selected.

SQL> 
SQL> disc
Disconnected from Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
With the Partitioning option
JServer Release 8.1.7.0.0 - Production
SQL>  

SQL> select to_date(sysdate,'mm/dd/yyyy hh24:mi:ss') from dual;
select to_date(sysdate,'mm/dd/yyyy hh24:mi:ss') from dual
               *
ERROR at line 1:
ORA-01843: not a valid month


SQL> 

why the error above. It should have converted to the right format . Am I missing something here

SQL> show parameters nls_date

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
nls_date_format                      string      DD-MON-RR
nls_date_language                    string

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

 

  1  select to_date(to_char(sysdate,'mm/dd/yyyy hh24:mi:ss'),
  2*                'mm/dd/yyyy hh24:mi:ss') from dual
SQL> /

TO_DATE(T
---------
21-JAN-06


why does this not give the date in requested format ? 

Amit, when you listed your NLS settings you showed the following. 

SQL> show parameters nls_date

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------
nls_date_format                      string      DD-MON-RR


if you want to see the timestamp information when you're in SQLPLUS you'll have to alter your NLS_DATE_FORMAT

it's simple enough to do on the session level, run the following when you log into a SQLPLUS session:

alter session set NLS_DATE_FORMAT='mm/dd/yyyy hh24:mi:ss'

or set the format to whatever you'd like in order to see your dates in that specific format. 

enjoy.  

Excellent help Sir...

_system_trig_enabled was set to false. I changed it and now it is working.

But in SYS user, date is shown in different format. 

Here is what is happening exactly...

SQL> select sysdate from dual;  (from other user)

SYSDATE
----------
07/02/2006

SQL> connect sys as sysdba
Connected.
SQL> select sysdate from dual;

SYSDATE
---------
07/02/200

SQL> connect ccis/filing
Connected.
SQL> select sysdate from dual;

SYSDATE
----------
07/02/2006

SQL> spool off;

In SYS user the year is shown as 200 only. What could be the possible reason ?

Thanks again for your help...

Kiran 

SQL> select * from nls_session_parameters
  2  where parameter = 'NLS_DATE_FORMAT';

PARAMETER                      VALUE
--------------------------------------------------
NLS_DATE_FORMAT                DD-Mon-YYYY HH24:MI

SQL> select fordate, wake_time from sleep_log;

FORDATE           WAKE_TIME
----------------- -----------------
08-Feb-2006 00:00 08-Feb-2006 07:30
09-Feb-2006 00:00 09-Feb-2006 08:00
10-Feb-2006 00:00 10-Feb-2006 07:45
07-Feb-2006 00:00 07-Feb-2006 08:00
01-Feb-2006 00:00 01-Feb-2006 07:45
31-Jan-2006 00:00 31-Jan-2006 08:00
02-Feb-2006 00:00 02-Feb-2006 07:30
03-Feb-2006 00:00 03-Feb-2006 08:30
06-Feb-2006 00:00 06-Feb-2006 07:37

9 rows selected.

SQL> alter session set nls_date_format='yyyy';

Session altered.

SQL> select fordate, wake_time from sleep_log where fordate='31-Jan-2006';
select fordate, wake_time from sleep_log where fordate='31-Jan-2006'
                                                       *
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string

This has behaved as I had expected - the format is set to YYYY and the conversion doesn't succeed. 

SQL> select fordate, wake_time from sleep_log where fordate='2006';

FORD WAKE
---- ----
2006 2006

Why does this give me only one row when there are 9 rows that should have been fetched? 

Hi

I have compatibility problem from Oracle 10G 
and the NLS_DATE_LANGUAGE parameter.

In 9i and 10g, the result of to_char() gives different 
results when using CANADIAN FRENCH.

Oracle 9i
==========================
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

SQL> alter session set nls_date_language='AMERICAN';

Session altered.

SQL> SELECT TO_CHAR(SYSDATE, 'DY MON') from dual;

TO_CHAR
-------
FRI FEB

SQL> alter session set nls_date_language='CANADIAN FRENCH';

Session altered.

SQL> SELECT TO_CHAR(SYSDATE, 'DY MON') from dual;

TO_CHAR
-------
VEN FEV



Oracle 10G
======================
SQL*Plus: Release 9.2.0.1.0 - Production on Fri Feb 24 13:41:59 2006

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> alter session set nls_date_language='AMERICAN';

Session altered.

SQL> SELECT TO_CHAR(SYSDATE, 'DY MON') from dual;

TO_CHAR
-------
FRI FEB


SQL> alter session set nls_date_language='CANADIAN FRENCH';

Session altered.

SQL> SELECT TO_CHAR(SYSDATE, 'DY MON') from dual;

TO_CHAR(SY
----------
VEN. FEVR.




How can we get the same results we had in Oracle 9i?


Thanks a lot
Oscar 

I know this is very basic stuff, but I have tried in almost all the servers I have access to around here.

I am trying to set nls_date_format environment variable before  login to sqlplus but it is not recognizing it for some reason. Can you point me into what I am doing wrong ? Thanks.

Here's an example:

# su - oracle
Sun Microsystems Inc.   SunOS 5.9       Generic May 2002
$ env | grep NLS
$ NLS_DATE_FORMAT="dd-mm-yyyy";export NLS_DATE_FORMAT
$ env | grep NLS
NLS_DATE_FORMAT=dd-mm-yyyy
$ sqlplus '/ as sysdba'

SQL*Plus: Release 10.1.0.5.0 - Production on Wed Jun 28 05:18:14 2006

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - 64bit Production
With the Partitioning and Data Mining options

SQL> select sysdate from dual;

SYSDATE
---------
28-JUN-06

SQL> show parameter nls_date_format

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
nls_date_format                      string
SQL>
SQL> !env | grep NLS
NLS_DATE_FORMAT=dd-mm-yyyy

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - 64bit Production
With the Partitioning and Data Mining options

I have tried with other users as well with no avail. Thanks! 

wondering if anybody else has run into this same issue.  with nls_date_format mm/dd/rr i get different behavior inserting dates through sqlplus in windows vs. linux.  In Linux inserting date 25-JUN-2006 fails with error Not a Valid month.  It does not fail and inserts the correct date in Windows.  Would expect this to fail in both.  I do not have any NLS environment variables set on either the Linux or Windows clients.

SQL> show parameter nls_date_format

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
nls_date_format                      string      mm/dd/rr

SQL> create table x (d date);

Table created.

On Linux Client ...
SQL> insert into x values ('25-JUN-2006');
insert into x values ('25-JUN-2006')
                      *
ERROR at line 1:
ORA-01843: not a valid month

On Windows Client ...
SQL> insert into x values ('25-JUN-2006');

1 row created.

SQL> select * from x;

D
---------
25-JUN-06 

Tom, could you please help me on to how do I avoid the date casting as string, and thus totally changing the meaning on this situation ?

SQL>
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE    9.2.0.1.0       Production
TNS for Compaq Tru64 UNIX: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production

SQL> select * from nls_session_parameters where parameter = 'NLS_DATE_FORMAT';

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_DATE_FORMAT                DD/MM/YYYY

SQL> select thedate from t;

THEDATE
----------
01/08/0000
01/08/0000
01/08/0000

SQL> select thedate||chr(44) from t;

THEDATE||CH
-----------
00/00/0000,
00/00/0000,
00/00/0000,

SQL>

You see, it totally changes the answer when I concatenate it with a comma (I am seeing this behaivour when using a technique of yours with sqlldr_exp to export the results to flat files).

Thanks! 

Lang = en_US -- This is already available in my UNIX environment

I have set the environment variable as 
set env NLS_DATE_FORMAT=YYMMDD 
from the $ prompt

Then logged through SQL*PLUS in the same session of my unix environment.

SQL> select sysdate from dual;

SYSDATE
---------
17-SEP-06

I would like to know what I am missing here..
and also would like to know whether NLS_LANG or NLS_DATE_FORMAT will take precedence if we have both.

Environment.

Oracle -- Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production 
Unix -- AIX 

 

Today is 04-Nov-2009 and I tried to run this query:
"select TO_CHAR(ROUND(SYSDATE, 'D')) from dual;" 
on my database server and here it is giving output as :

COREACC> select TO_CHAR(ROUND(SYSDATE, 'D')) from dual;

TO_CHAR(R
---------
01-NOV-09


And again I tried same query from sql*plus on same database with same user and here it gives output something like this:

SQL> select to_char(trunc(sysdate,'D')) from dual;

TO_CHAR(T
---------
02-NOV-09


could you please elabourate this issue , is it the issue of Oracle or Interface? my sql*plus version is :

Connected to:
Oracle9i Enterprise Edition Release 9.0.1.3.0 - 64bit Production
With the Partitioning option
JServer Release 9.0.1.3.0 - Production


Thanks in Advance,
Gaurav Jain

The CREATE command I included is the exact command I used to create my test table.  There aren't any triggers, indexes or foreign keys on this table.  I made it as vanilla as I could.

Here is the SQL Plus of my INSERT -

SQL> insert into cd_third_party_info (tcsi_office_id, admin_notes) values ('S.1234.00', 'this is a 
  2  test')
  3  ;
insert into cd_third_party_info (tcsi_office_id, admin_notes) values ('S.1234.00', 'this is a
*
ERROR at line 1:
ORA-01843: not a valid month

I forgot to include two pieces of information, first the database has the NLS_DATE_FORMAT set to MM/DD/RR HH24:Mi:SS
Secondly, my developer fixed the ORA-01843 by doing this -

alter table CD_THIRD_PARTY_INFO modify (credit_score_date default to_date('01/01/1970','mm/dd/yyyy'));

As a DBA I'm interested in knowing why Oracle is having this issue in the first place.

scott@SRID> select to_char(to_date('31092010','ddmmyyyy','nls_calendar=''gregorian'''),'dd-mon-rrrr','nls_calendar=''gregorian''') from dual;
select to_char(to_date('31092010','ddmmyyyy','nls_calendar=''gregorian'''),'dd-mon-rrrr','nls_calendar=''gregorian''') from dual
                       *
ERROR at line 1:
ORA-01839: date not valid for month specified

scott@SRID> select to_char(to_date('31091410','ddmmyyyy','nls_calendar=''english hijrah'''),'dd-mon-rrrr','nls_calendar=''english hijrah''') from dual;

TO_CHAR(TO_DATE('31091410','DDMMYYYY','NLS_CALENDAR=''ENGLISHHIJRAH'''),'DD-MON-RRRR','NLS_CALENDAR=
----------------------------------------------------------------------------------------------------
01-Shawwal          -1410