how to change the default date format in oracle

Question

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?

• joe: I think i got it, someone had the same issue posted on SOF: stackoverflow.com/questions/8134493/… Now what i would like to know if possible is how to drop tables whose names follow a certain pattern, let's say i have 100 tables with names using TT_TableName... i would like to find a command that will drop all of those table so i can start fresh, thanks
• Justin Cave: That sounds like a separate question. You'd need to create a separate thread for your drop question.
• joe: problem's solved.
• user1822: Do not rely on implicit data type conversion - never. Always use ISO date literals DATE '2012年06月15日' or to_date() with an explicit format mask tat uses numbers only. Even abbreviated month names (FEB or DEC) might also not work on every computer.

Answer 1

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

Answer 2

You generally don't want to rely on implicit conversions of strings to dates. That only leads to pain and suffering since different users will have different date formats. Either use ANSI date literals or use the to_date function with an explicit format mask

UPDATE bb_product
 SET salestart = date '2012-06-01',
 saleend = date '2012-06-15',
 saleprice = 8
 WHERE idProduct = 6

or

UPDATE bb_product
 SET salestart = to_date('01-JUN-2012', 'DD-MON-YYYY'),
 saleend = to_date('15-JUN-2012', 'DD-MON-YYYY'),
 saleprice = 8
 WHERE idProduct = 6

• user1822: I would go as far as to also ban month names in a to_char() call. Even the abbreviated month names are subject to NLS settings (DEC vs. DEZ, FEB vs. FEV, ...)
• Justin Cave: @a_horse_with_no_name - You mean in a to_date call, right, not to_char? Getting the month name in the current NLS settings would seem to be a feature in the to_char call and a potential bug in the to_date call. Of course, you could modify the to_date call to specify the NLS parameters as well to take care of that issue but that gets even more verbose. That's why I would generally much prefer the ANSI literals.
• user1822: Yes, sorry I meant to_date(). to_date('15-DEC-2012', 'DD-MON-YYYY') would fail on my computer, whereas to_date('15-12-2012', 'DD-MM-YYYY') will work anywhere - much like the - shorter - ISO literals (which I prefer as well)
• Raymond Nijland: @Justin Cave i've just run into pain with the ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD'; method only... WHERE date = '2014年02月05日' fails where WHERE date >= '2014年02月05日' or WHERE TO_CHAR(date, 'YYYY-MM-DD') = '2014年02月05日' works; this one is the best answer to avoid headpains by banging your head on the desk...