CODEWITHSUNDEEP
sqloracle-databaseoracle-sqldeveloper
vermap
vermap

Reputation: 231

How to use date variable in sql developer's "Enter Binds" dialog?

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

I used all the possible combinations to format date using to_date() function. Every time getting below exception:

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

Sorry can't post image here

Upvotes: 16

Views: 46182

Answers (6)

Neel Alex
Neel Alex

Reputation: 733

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

note that you should not use quotes. Its implicitly done by SQL developer. Happy Coding!

Upvotes: 0

Blza Box
Blza Box

Reputation: 345

Just copying the answer from Oracle Community forum:

You should be able to enter dates which conform to your NLS_DATE_FORMAT setting.

e.g. If NLS_DATE_FORMAT is DD-MON-YYYY, you can enter 24-jan-2011 for today's date.

Worked for me.

Upvotes: 8

atavio
atavio

Reputation: 1295

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

Upvotes: 2

Mark J. Bobak
Mark J. Bobak

Reputation: 14403

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'.

Upvotes: 14

DPA
DPA

Reputation: 154

It's not possible. Probably because SQL plus doesn't have it.

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

Upvotes: 1

tbone
tbone

Reputation: 15473

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).

Upvotes: 4

Related Questions