无用的 Oracle 错误消息：预期 %s，使用 to_date 得到 %s

发布于 2024-12-01 23:33:46 字数 458 浏览 0 评论 0原文

这是一个简单的查询：

SELECT COUNT(*) FROM m_bug_t 
WHERE date_submitted BETWEEN TO_DATE('2011-08-22','yyyy-mm-dd') AND TO_DATE('2011-08-29','yyyy-mm-dd') 
AND status != 100

给出以下错误消息

ORA-00932: inconsistent datatypes: expected NUMBER got DATE
00932. 00000 -  "inconsistent datatypes: expected %s got %s"
*Cause:    
*Action:
Error at Line: 2 Column: 22

有什么想法吗？我习惯使用 MySQL，即使没有 to_date 函数，它也能工作。

原文

Here is a simple query:

SELECT COUNT(*) FROM m_bug_t 
WHERE date_submitted BETWEEN TO_DATE('2011-08-22','yyyy-mm-dd') AND TO_DATE('2011-08-29','yyyy-mm-dd') 
AND status != 100

that gives the following error message

ORA-00932: inconsistent datatypes: expected NUMBER got DATE
00932. 00000 -  "inconsistent datatypes: expected %s got %s"
*Cause:    
*Action:
Error at Line: 2 Column: 22

Any ideas? I'm using to using MySQL where this works even without the to_date function.

分享到QQ

分享到微博

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

发布评论

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

眼睛会笑 2024-12-08 23:33:46

date_subscribed 列似乎是数字，您正尝试将其与日期进行比较。 Oracle 不会让你这样做。

[编辑:] 假设 Epoch 是 1970 年 1 月 1 日，您应该能够使用：

TO_DATE('01/01/1970 00:00:00', 'MM-DD-YYYY HH24:MI:SS') + (date_submitted / (24 * 60 * 60))

获取所表示的实际日期。我不确定这是否 100% 准确，因为您的日期（以秒为单位）可能不包含闰秒，而 Oracle 可能包含闰秒。

It looks like the date_submitted column is numeric, and you're trying to compare it to a date. Oracle won't let you do this.

[EDIT:] Assuming that the Epoch is Jan 1, 1970, you should be able to use:

TO_DATE('01/01/1970 00:00:00', 'MM-DD-YYYY HH24:MI:SS') + (date_submitted / (24 * 60 * 60))

To get the actual date that is represented. I'm not sure if this will be 100% accurate, since your date in seconds may not include leap seconds and Oracle's likely does.

回复收藏 0 原文

薄凉少年不暖心 2024-12-08 23:33:46

将 Oracle 日期转换为 unix 时间戳值需要以下函数：

SELECT (sysdate - to_date('01-JAN-1970','DD-MON-YYYY')) * (86400) as dt FROM dual;

或者在 sql where 子句的情况下：

WHERE date_submitted between 
    ((TO_DATE('2011-08-22', 'yyyy-mm-dd') - to_date('01-JAN-1970','DD-MON-YYYY')) * (86400))
AND 
    ((TO_DATE('2011-08-29', 'yyyy-mm-dd') - to_date('01-JAN-1970','DD-MON-YYYY')) * (86400))

Converting Oracle dates to unix timestamp values requires the following function:

SELECT (sysdate - to_date('01-JAN-1970','DD-MON-YYYY')) * (86400) as dt FROM dual;

or in the case of your sql where clause:

WHERE date_submitted between 
    ((TO_DATE('2011-08-22', 'yyyy-mm-dd') - to_date('01-JAN-1970','DD-MON-YYYY')) * (86400))
AND 
    ((TO_DATE('2011-08-29', 'yyyy-mm-dd') - to_date('01-JAN-1970','DD-MON-YYYY')) * (86400))

回复收藏 0 原文

~没有更多了~