0

I am using oracle 12c with the username system. My problem is when I execute this insert statement that I took from oracle live sql site:

insert into emp 
values(7788, 'SCOTT', 'ANALYST', 7566,to_date('13-JUL-87','dd-mm-rr') - 85,3000, null, 20);

it shows :

sql error ora-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.

what is this -85 after the to_date(..)

APC
  • 144,005
  • 19
  • 170
  • 281
Zain Elabidine
  • 349
  • 5
  • 16
  • 1
    ‘JUL’ matches format element `MON`, it is not a month number as expected by `MM`. BTW, consider using 4-digit years, and date literals - or at least month numbers, which aren’t NLS-dependent, instead of names or abbreviations. – Alex Poole Jan 27 '18 at 19:04

1 Answers1

2

To handle dates, you would better use the ANSI format (date 'yyyy-mm-dd'):

insert into emp values(7788, 'SCOTT', 'ANALYST', 7566, date '1987-07-13'- 85,3000, null, 20);

If you need to use a to_date for some reason, you have to be sure that the format of your string exactly matches the format mask you use: if your month is written as 'JUL' you need 'MON' in the format mask and not 'mm'. 'mm' would match a month written as '07'.

Please notice that even with the right format mask, this way to write dates is dangerous, because it's based on the language of your DB.

The -85 means "subtract 85 days".

Aleksej
  • 22,443
  • 5
  • 33
  • 38