How to change the default date format in oracle

datedate formaterrorsoracle

New to Oracle here, i am working on some assigments but i noticed when i was creating the tables using a script, oracle throws the following error:

ORA-01858: a non-numeric character was found where a numeric was expected
01858. 00000 -  "a non-numeric character was found where a numeric was expected"
*Cause:    The input data to be converted using a date format model was
       incorrect.  The input data did not contain a number where a number was
       required by the format model.
*Action:   Fix the input data or the date format model to make sure the
       elements match in number and type.  Then retry the operation.

knowing that the date format in my queries is something like '01-JUN-2012'
i was wondering how can i change the default format in my oracle installation to meet my system format? I am using SQL Developer with the Free Oracle version 11g.

also i am wondering if this is because my computer is WIN 7 french version and teh dates in my queries are in the English US format?

UPDATE bb_product
   set salestart = '01-JUN-2012', saleend = '15-JUN-2012', SalePrice = 8.00
   WHERE idProduct = 6;

this query is from the Brewer Beans DB that my professor handed out. an which i am assuming was created in a system that uses the EN-US format…any help?

Best Answer

You can try the ALTER SESSION 

ALTER SESSION SET NLS_DATE_FORMAT = '[date_format]';

date_formats

MM  Numeric month (e.g., 07)
MON     Abbreviated month name (e.g., JUL)
MONTH   Full month name (e.g., JULY)
DD  Day of month (e.g., 24)
DY  Abbreviated name of day (e.g., FRI)
YYYY    4-digit year (e.g., 1998)
YY  Last 2 digits of the year (e.g., 98)
RR  Like YY, but the two digits are ``rounded'' to a year in the range 1950 to 2049. Thus, 06 is considered 2006 instead of 1906
AM (or PM)  Meridian indicator
HH  Hour of day (1-12)
HH24    Hour of day (0-23)
MI  Minute (0-59)
SS  Second (0-59)

TO_CHAR(datum_column, date_format) is the best method to avoid headpains check Justin Cave's answer

Related Question