ORA-01855：AM / A.M。或PM / P.M。需要 [英] ORA-01855: AM/A.M. or PM/P.M. required

问题描述

我收到错误： ORA-01855：AM / A.M。或PM / P.M。当我尝试执行以下查询时，需要

。

  INSERT INTO TBL(ID,START_DATE) 
    values (123, TO_DATE ('3/13/2012 9:22:00 AM', 'MM/DD/YYYY HH:MI AM'))

START_DATE 列的类型为Date。

Where my START_DATE column is of type "Date".

我已执行以下查询，它没有发生错误，仍然没有在上述问题中成功：

I have executed following query and it gave no errors, still not success yet in above issue:

ALTER SESSION SET NLS_DATE_FORMAT = "MM/DD/YYYY HH:MI AM";

推荐答案

您的格式掩码必须与字符串的格式匹配正在转换。因此，您可以将 SS 添加到格式掩码或从字符串中删除秒数

Your format mask must match the format of the string you are converting. So you would either want to add SS to the format mask or remove the seconds from the string

INSERT INTO TBL(ID,START_DATE) 
  values (123, TO_DATE ('3/13/2012 9:22:00 AM', 'MM/DD/YYYY HH:MI:SS AM'))

或

INSERT INTO TBL(ID,START_DATE) 
  values (123, TO_DATE ('3/13/2012 9:22 AM', 'MM/DD/YYYY HH:MI:SS AM'))

如果要接受包含秒的字符串，但不想存储数据库中的秒数（在这种情况下，Oracle将始终存储0秒），您可以使用 TRUNC 函数

If you want to accept a string that contains seconds but you don't want to store the seconds in the database (in which case Oracle will always store 0 for the seconds), you can use the TRUNC function

INSERT INTO TBL(ID,START_DATE) 
  values (123, TRUNC( TO_DATE ('3/13/2012 9:22:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 'MI') )

这篇关于ORA-01855：AM / A.M。或PM / P.M。需要的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！