如何在 sql 开发人员的“输入绑定”对话框中使用日期变量？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22717575/
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
How to use date variable in sql developer's "Enter Binds" dialog?
提问by vermap
I am trying to run a query from sql developer and query has variables (:var). I am having problem with the date variables.
我正在尝试从 sql 开发人员运行查询，并且查询具有变量 ( :var)。我对日期变量有问题。
I used all the possible combinations to format date using to_date()function.
Every time getting below exception:
我使用了所有可能的组合来使用to_date()函数来格式化日期。每次低于异常：
ORA-00932: inconsistent datatypes: expected DATE got NUMBER
00932. 00000 - "inconsistent datatypes: expected %s got %s"
*Cause:
*Action:
Sorry can't post image here
抱歉这里不能发图片
采纳答案by Mark J. Bobak
Try changing your query to be:
尝试将您的查询更改为：
select first_name,
last_name,
dob,
org
from emp
where dob > to_date(:highDate,'DD-MON-YYYY');
then when prompted, enter '20-JAN-2010'.
然后在出现提示时，输入'20-JAN-2010'。
回答by AntonLosev
Just copying the answer from Oracle Community forum:
只需复制 Oracle 社区论坛的答案：
You should be able to enter dates which conform to your
NLS_DATE_FORMATsetting.e.g. If
NLS_DATE_FORMATisDD-MON-YYYY, you can enter24-jan-2011for today's date.
您应该能够输入符合您
NLS_DATE_FORMAT设置的日期 。例如，如果
NLS_DATE_FORMAT是DD-MON-YYYY，您可以输入24-jan-2011今天的日期。
Worked for me.
对我来说有效。
回答by tbone
Try using a substitution variable. For example:
尝试使用替代变量。例如：
select (&var - 1) from dual;
sql developer will ask you to enter a substitution variable value, which you can use a date value (such as sysdate or to_date('20140328', 'YYYYMMDD') or whatever date you wish).
sql 开发人员会要求您输入替换变量值，您可以使用日期值（例如 sysdate 或 to_date('20140328', 'YYYYMMDD') 或您希望的任何日期）。
回答by atavio
Try with:
尝试：
SELECT TO_DATE(:my_var, 'dd.mm.yyyy') my_date from dual;
and then enter something like 01.02.2017(without ') as the value of :my_var
然后输入类似01.02.2017（没有'）作为的值:my_var
回答by Neel Alex
In the query provide the date in the below format
在查询中提供以下格式的日期
to_date(:var,'DD-MON-YYYY')
and while executing the query from sql developer, provide the value for var bind parameter as 29-DEC-1995
并在从 sql 开发人员执行查询时，将 var 绑定参数的值提供为 29-DEC-1995
note that you should notuse quotes. Its implicitly done by SQL developer. Happy Coding!
请注意，您不应使用引号。它由 SQL 开发人员隐式完成。快乐编码！
回答by drx
I don't understand why (even if I could imagine...), but:
我不明白为什么（即使我能想象......），但是：
- this select statement works: :date_var = 09122019
- 这个选择语句有效：：date_var = 09122019
select * from t where date_field = to_date(:date_var,'ddmmyyyy');
select * from t where date_field = to_date(:date_var,'ddmmyyyy');
- the similar delete or update statement doesn't work: :date_var = 09122019
- 类似的删除或更新语句不起作用：:date_var = 09122019
delete t where date_field = to_date(:date_var,'ddmmyyyy');
delete t where date_field = to_date(:date_var,'ddmmyyyy');
with this error: ORA-01847: day of month must be between 1 and last day of month
出现此错误：ORA-01847: 月份的日期必须介于 1 和月份的最后一天之间
Using delimiting caracters like '/' or '.', the delete works: :date_var = 09/12/2019
使用像“/”或“.”这样的分隔符，删除有效：:date_var = 09/12/2019
delete t where date_field = to_date(:date_var,'dd/mm/yyyy');
delete t where date_field = to_date(:date_var,'dd/mm/yyyy');
回答by DPA
It's not possible. Probably because SQL plus doesn't have it.
这是不可能的。可能是因为 SQL plus 没有它。
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2939749100346967872
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2939749100346967872
