由于非数字字符错误,插入失败

发布于 2024-10-05 11:06:00 字数 1040 浏览 7 评论 0原文

hy

表:

create table Players (PlayerNo number (4) not null, Name varchar2(15), date_of_birth date,leagno varchar(4));

错误插入:

insert into PLAYERS (PlayerNo,Name,date_of_birth,leagno) VALUES (1,'Philipp K','Jan-10-1999','1')

出了什么问题?

错误代码:

Fehler beim Start in Zeile 1 in Befehl:
insert into PLAYERS (PlayerNo,Name,date_of_birth,leagno) VALUES (1,'Philipp K','Jan-10-1999','1')
Fehlerbericht:
SQL-Fehler: ORA-01858: Ein nicht-numerisches Zeichen wurde gefunden, während ein numerisches Zeichen erwartet wurde
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.

hy

table:

create table Players (PlayerNo number (4) not null, Name varchar2(15), date_of_birth date,leagno varchar(4));

wrong insert:

insert into PLAYERS (PlayerNo,Name,date_of_birth,leagno) VALUES (1,'Philipp K','Jan-10-1999','1')

whats wrong?

error code:

Fehler beim Start in Zeile 1 in Befehl:
insert into PLAYERS (PlayerNo,Name,date_of_birth,leagno) VALUES (1,'Philipp K','Jan-10-1999','1')
Fehlerbericht:
SQL-Fehler: ORA-01858: Ein nicht-numerisches Zeichen wurde gefunden, während ein numerisches Zeichen erwartet wurde
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.

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(3

初与友歌 2024-10-12 11:06:00

*原因:输入数据使用日期格式模型进行转换
曾是
不正确。输入数据不包含数字,其中
数量是
格式模型所要求的。

您使用的日期字符串与 oracle 期望的不匹配。默认格式 iirc 是 DD-Mon-YYYY,而不是您尝试使用的 Mon-DD-YYYY。

*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.

The date string you're using doesn't match what oracle is expecting. The default format, iirc, is DD-Mon-YYYY, not Mon-DD-YYYY as you're trying to use.

幸福丶如此 2024-10-12 11:06:00
INSERT
INTO PLAYERS
  (
    PlayerNo,
    Name,
    date_of_birth,
    leagno
  )
  VALUES
  (
    1,
    'Philipp K',
    TO_DATE('Jan-10-1999','Mon-dd-yyyy'),
    '1'
  )

您需要使用 TO_DATE 以正确的格式提供日期。

INSERT
INTO PLAYERS
  (
    PlayerNo,
    Name,
    date_of_birth,
    leagno
  )
  VALUES
  (
    1,
    'Philipp K',
    TO_DATE('Jan-10-1999','Mon-dd-yyyy'),
    '1'
  )

You need to supply date using TO_DATE with the right format.

海风掠过北极光 2024-10-12 11:06:00

错误解释如下:

“要转换的输入数据使用
日期格式模型不正确。
输入数据不包含
需要数字的数字
格式模型”

这意味着您传递给 date_of_birth 列的值格式错误。默认情况下,Oracle 希望日期采用 DD-MON-YYYY 格式。您传递的日期采用 MON-DD 格式-YYYY。

有两种(或者三种)方法

  1. 可以使用显式格式掩码:to_date('Jan-10-1999', 'MON-DD-YYYY')
  2. 更改NLS_DATE_FORMAT 参数,在会话级别甚至数据库级别F在此处了解更多信息此处
  3. 将插入语句更改为以预期格式传递日期。

The error explains:

"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"

What this means is the value you are passing to the date_of_birth column is in the wrong format. By default Oracle expects dates to be in the format DD-MON-YYYY. You are passing a date in the format MON-DD-YYYY.

There are two - well three - ways of dealing with this.

  1. Use an explicit format mask: to_date('Jan-10-1999', 'MON-DD-YYYY')
  2. change the NLS_DATE_FORMAT parameter, at the session or even database level. Find out more here and here.
  3. change your insert statement to pass the date in the expected format.
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文