Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
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 –  joe Jan 29 at 20:02
    
That sounds like a separate question. You'd need to create a separate thread for your drop question. –  Justin Cave Jan 29 at 20:35
    
problem's solved. –  joe Jan 29 at 21:39
1  
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. –  a_horse_with_no_name Jan 29 at 21:53
add comment

2 Answers

up vote 1 down vote accepted

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

share|improve this answer
add comment

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
share|improve this answer
    
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, ...) –  a_horse_with_no_name Jan 29 at 21:54
    
@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. –  Justin Cave Jan 29 at 21:56
    
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) –  a_horse_with_no_name Jan 29 at 22:00
    
@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... –  Raymond Nijland Feb 5 at 10:09
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.