oracle 要求用户在 sql 中输入日期给出 ORA-00932：不一致的数据类型：预期的日期得到 NUMBER 错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11759515/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Asking User to input date in sql giving ORA-00932: inconsistent datatypes: expected DATE got NUMBER Error
提问by Algorithmist
I am trying to input a date value from the user and then using that value in the query.
我试图从用户输入一个日期值，然后在查询中使用该值。
select * from TB_MNP_GTY_TRANS_STEPS where CREATE_DATETIME>=&startdate
Now when i run the sql statement in Toad and input 8/1/2012 as date data type i am getting
现在，当我在 Toad 中运行 sql 语句并输入 8/1/2012 作为日期数据类型时，我得到
ORA-00932: inconsistent datatypes: expected DATE got NUMBER
ORA-00932: 不一致的数据类型：预期的 DATE 得到 NUMBER
Can someone suggest where i am wrong.Note that CREATE_DATETIME is of Date Type.
有人可以建议我错在哪里。注意 CREATE_DATETIME 是日期类型。
回答by Ollie
You should really specify what date format you are using in your parameter:
您应该真正指定您在参数中使用的日期格式：
SELECT *
FROM TB_MNP_GTY_TRANS_STEPS
where CREATE_DATETIME >= TO_DATE(&startdate, 'DD/MM/YYYY');
Read about date formats here
Currently your session is expecting the date to be in its default NLS_DATE default fomat and obviously the format of the date you're entering is different. Explicitly specifying date formats prevents this issue from occurring.
当前，您的会话期望日期采用其默认的 NLS_DATE 默认格式，显然您输入的日期格式不同。显式指定日期格式可防止发生此问题。
Hope it helps...
希望能帮助到你...
EDIT:If you want to pass in the 8th January 2012 then you could specify your variable value as:
编辑：如果您想在 2012 年 1 月 8 日通过，那么您可以将变量值指定为：
08/01/2012
And your select would be:
您的选择将是：
SELECT *
FROM TB_MNP_GTY_TRANS_STEPS
where CREATE_DATETIME >= TO_DATE(&startdate, 'DD/MM/YYYY');
Depending upon your environment you might need to wrap the variable in single quotes (for TOAD you definiely will) i.e.
根据您的环境，您可能需要将变量用单引号括起来（对于 TOAD，您肯定会）即
SELECT *
FROM TB_MNP_GTY_TRANS_STEPS
where CREATE_DATETIME >= TO_DATE('&startdate', 'DD/MM/YYYY');
The error you are getting is caused by the format of the date string you are entering not matching EXACTLY the format you are specifying (see the leading "0" before the 8and 1in the day and month!)
你所得到的错误是由你进入不完全匹配您所指定的日期格式字符串格式化造成的（见领导“ 0”之前8和1在当天和一个月！）
回答by Maddy
Date casting necessary
需要日期转换
select * from TB_MNP_GTY_TRANS_STEPS where CREATE_DATETIME>=to_date(&startdate, 'MM-DD-YYYY')
and while passing parameter you should pass value in quoets as '08-09-1999'
并且在传递参数时，您应该将 quoets 中的值传递为“08-09-1999”
