Oracle数据类型错误 [英] Oracle datatype error

问题描述

我试图通过从源表中选择一个值来将值插入到date数据类型中，该源表的列也是date数据类型.我直接选择了该列，而没有使用to_date函数进行任何转换，因为两者是相同的类型，但出现以下错误:

I'm trying to insert a value into date datatype by selecting a value from a source table whose column is also date datatype. I have selected the column directly without doing any conversion using to_date function, because both are same types but I'm getting the following error:

SQL Error: ORA-00932: inconsistent datatypes: expected DATE got NUMBER
00932. 00000 -  "inconsistent datatypes: expected %s got %s"

我已经仔细检查过，源列没有空值.

I had doubled checked, source column has no null values.

insert into Target(Targetdate) 
  select to_date(Source.START_DATE,'yyyy-mm-dd')
    from Source

感谢您对此进行调查.

Thanks for looking into this.

推荐答案

我假设您尝试将截短到某天的日期保存到Target表中.可以这样做:

I assume you try to get the dates truncated to the day into the Target table. This can be done by doing so:

insert into Target(Targetdate) select trunc(Source.START_DATE,'DD') from Source

编辑

Dazzal提到，由于这是将第二个参数留给TRUNC时的默认操作，因此它甚至更简单一些:

Dazzal mentioned that as this is the default operation when leaving out the second parameter to TRUNC, so this is even a bit simpler:

insert into Target(Targetdate) select trunc(Source.START_DATE) from Source

查询的问题是Source.START_DATE是日期，而不是字符串...

The problem with your query was that Source.START_DATE is a date, not a string...

EDIT2

似乎您想获取特定格式的日期字符串-与日期类型无关，并且没有内部存储thig的方式-只需执行以下操作:

As it seems that you want to get date strings in a specific format - which has nothing to do with the date type, and how thigs are stored internally - just do this:

SELECT to_char(START_DATE,'YYYY-MM-DD' from Source;

• Oracle TRUNC
• Oracle TRUNC

这篇关于Oracle数据类型错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！