Binding a date literal to a parameter in an Oracle SQL Developer query

Question

I have a query in Oracle that looks (in part) like this:

where Doc3.clinicalDate >= ml.convert_date_to_id(:DateBegin)
    and Doc3.clinicalDate < ml.convert_date_to_id(:DateEnd)

When I put this query in SSRS and set up the report and dataset parameters, it works fine. However, if I paste it into Oracle SQL Developer, it pops up a window asking me to "Enter Binds". When I put in "date '2011-06-01'", I get the error "ORA-01858: a non-numeric character was found where a numeric was expected". How do I pass it the date in a format it will actually understand? Thanks.

That would depend on the default date format for your database. Try 06-01-2011, 01-Jun-2011, 2011-06-01 (note: all without surrounding apostrophes which would make them strings). — Bob Jarvis - Слава Україні
– Bob Jarvis - Слава Україні, Commented May 29, 2012 at 20:33
01-JUN-2011 was the ticket. :-) Would you be so kind as to put this as an answer so I can accept it? — SarekOfVulcan
– SarekOfVulcan, Commented May 29, 2012 at 20:42

Bob Jarvis - Слава Україні · Accepted Answer · 2012-05-30 10:56:13Z

5

That would depend on the default date format for your database. Try 06-01-2011, 01-Jun-2011, 2011-06-01 (note: all without surrounding apostrophes which would make them strings).

answered May 30, 2012 at 10:56

Bob Jarvis - Слава Україні

50.2k10 gold badges81 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Grzegorz W · Accepted Answer · 2012-05-29 20:32:41Z

3

That's a common problem. I change my query to use to_date(:DateEnd,'DD/MM/YYYY') - than it takes your date as varchar and converts to date.

answered May 29, 2012 at 20:32

Grzegorz W

3,5291 gold badge24 silver badges23 bronze badges

Collectives™ on Stack Overflow

Binding a date literal to a parameter in an Oracle SQL Developer query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related