ORA-01840：输入值不足以使用Select在Oracle Insert中显示日期格式 [英] ORA-01840: input value not long enough for date format in Oracle Insert using Select

问题描述

我在下面的查询中，由于 ORA-01840而出现错误：输入值的长度不足以表示日期格式。 C_DATE 列是日期数据类型。

I have below query where i am getting error as ORA-01840: input value not long enough for date format. The C_DATE column is Date datatype.

INSERT INTO CS_LOG(NAME, ID, C_DATE)
Select MAX(ML.NAME), ML.ID, TO_CHAR(CHK_DATE,'YYYYMM')
from D_ID ML,
     CS_LOG MD
WHERE ML.NAME != MD.NAME
  and ML.ID != MD.ID
  and MD.C_DATE = LAST_DAY(to_date(sysdate,'YYYYMMDD'))
GROUP BY ML.ID, C_DATE;

推荐答案

您不能使用格式插入日期'。日期具有内部表示，它们始终具有所有日期/时间组成部分，然后可以根据需要进行格式化以进行显示。

You can’t insert a date ‘with a format’. Dates have an internal representation, they always have all the date/time components and can then be formatted for display however you want.

您生成为YYYYMM的字符串是被插入内容隐式转换为日期，因为这是目标列的数据类型。该隐式转换使用的是您的NLS设置，因此期望使用更长的值来匹配NLS日期格式。您的字符串与该隐式格式不匹配，这会导致您看到错误。

The string you’re generating as YYYYMM is being implicitly converted to a date by the insert, as that is the target column’s data type. That implicit conversion is using your NLS settings, and from that is expecting a longer value to match the NLS date format. Your string doesn’t match that implicit format, which is causing the error you’re seeing.

如果您只对年份和月份感兴趣，则最接近您的年份可以得到的是在一个月的第一天午夜存储，这可以通过 trunc 获得：

If you’re only interested in the year and month then the closest you can get is to store midnight on the first of the month, which you can get with trunc:

INSERT INTO CS_LOG(NAME, ID, C_DATE)
Select MAX(ML.NAME), ML.ID, TRUNC(CHK_DATE,'MM')
from D_ID ML,CS_LOG MD
WHERE ML.NAME != MD.NAME and ML.ID != MD.ID
and MD.C_DATE = LAST_DAY(sysdate)
GROUP BY ML.ID,C_DATE;

我还删除了迄今为止多余的 呼叫。您还应该考虑切换到ANSI连接语法。

I’ve also removed the extra to_date call. You should consider switching to ANSI join syntax too.

然后您可以将 c_date 格式化为YYYYMM以便在查询时显示如果需要的话，通过 to_char 。

You can then format c_date as YYYYMM for display when you query it, if that is what you need, via to_char.

这篇关于ORA-01840：输入值不足以使用Select在Oracle Insert中显示日期格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！