How to use date variable in sql developer to enter bind dialog box?

Question

I am trying to run a query from a sql developer, and the query has variables ( :var ). I have a problem with date variables.

I used all possible combinations to format the date using the to_date() function. Every time you get an exception:

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

Sorry, I can not post the image here.

Answer 1

Try changing your query:

 select first_name, last_name, dob, org from emp where dob > to_date(:highDate,'DD-MON-YYYY'); 

then when prompted, enter '20-JAN-2010' .

Answer 2

Just copy the answer from the Oracle community forum:

You should be able to enter dates that match your NLS_DATE_FORMAT .

for example, If NLS_DATE_FORMAT is DD-MON-YYYY , you can enter 24-jan-2011 for today.

Worked for me.

Answer 3

Try using a replacement variable. For example:

 select (&var - 1) from dual; 

Developer

sql will ask you to enter a substitution variable value in which you can use a date value (e.g. sysdate or to_date ('20140328', 'YYYYMMDD') or any other date you want).

Answer 4

Try:

 SELECT TO_DATE(:my_var, 'dd.mm.yyyy') my_date from dual; 

and then enter something like 01.02.2017 (without ' ) as the value :my_var

Answer 5

It's impossible. Probably because SQL Plus does not have it.

https://asktom.oracle.com/pls/asktom/f?p=100:11:59::::P11_QUESTION_ID:2939749100346967872

Answer 6

In the request, specify the date in the following format

 to_date(:var,'DD-MON-YYYY') 

and when executing a query from sql developer, specify the value for var bind parameter as 29-DEC-1995

note that you must not use quotation marks. This is implicitly done by the SQL developer. Good coding!