trunc（date）总是给出错误 [英] trunc(date) always give an error

问题描述

Hiii

看这个请求，告诉我为什么总是Oracle给我这个错误

Hiii

look at this request and tell me why always Oracle give me this error

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

注意1：当我删除 TRUNK（）它的工作，但我去把它放在请求

注2：我使用这个日期格式 1/07/2014 或 1-07-2014 和 1 / JUL / 2014 总是相同的错误

note 1 : that when I remove TRUNK() it's work but I went to put it on the request

note 2 : I use this date format 1/07/2014 or 1-07-2014 and 1/JUL/2014 always the same error

这是我的请求

  SELECT 
  QTYORDERED,
  LINENETAMT
FROM C_Orderline
INNER JOIN C_Order
ON (C_Orderline.C_Order_ID     = C_Order.C_Order_ID)
WHERE C_Order.ad_org_id   = 1401007
AND C_order.DOCSTATUS   = 'CO'
AND (TRUNC('1-jul-2014' )     IS NULL
OR C_order.DATEORDERED >= TRUNC('1-jul-2014'))
AND (TRUNC('4-sep-2014')      IS NULL
OR C_order.DATEORDERED <= TRUNC('4-sep-2014'))
ORDER BY c_order.ad_org_id,C_order.DATEORDERED DESC

推荐答案

我支持的问题是您的列 DATEORDERED 这是 NUMBER 可能

I supsect the problem is with your column DATEORDERED which is NUMBER probably

这里是一个例子。

SQL>  select * from dual where trunc(to_DATE('04-SEP-2014','DD-MON-YYYY')) > 100;
 select * from dual where trunc(to_DATE('04-SEP-2014','DD-MON-YYYY')) > 100
                                                                      *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected DATE got NUMBER

这个不会抛出错误。因为Oracle认识到 04-SEP-2014 作为日期

And this wont throw error. Since, Oracle recognise 04-SEP-2014 as Date

SQL> select * from dual where trunc(to_DATE('04-SEP-2014','DD-MON-YYYY')) > '04-SEP-2014';

no rows selected

所以，你必须分析当前数据它。任何与...匹配的日期格式。
像' 20140409 ' - >' YYYYDDMM '

So, you have to analyse the current data in it. Fo any date format it matches with..
Something like '20140409' -> 'YYYYDDMM'

并尝试 TO_DATE（C_order.DATEORDERED，'YYYYDDMM'）< = TRUNC（TO_DATE（'4-sep-2014'''DD-mon -yyyy'））

这篇关于trunc（date）总是给出错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！