date between date1 and date2 keeps error with ORA-01858: non-numeric character found where a numeric was expected.

Question

I am creating a query in SSRS, I have my parameters defines as DATE/TIME, and the field I am querying is stores as DATE, but when I write the code:

select * from <table> WHERE Date between @Date1 and @Date2

it keeps throwing the error: ORA-01858: non-numeric character found where a numeric was expected. I've tried placing single quotes around @Date1 and @Date2, I've tried to add +'00:00:00' on the ends for time, I've tried CAST and CONVERT and still error's out. 

If anyone can shed some light on my code, I'd appreciate it. 

Answer 1

Whats the data type of @Date1 AND @Date2?

Can you try this and see if it works fine?

select * from <table> WHERE Date >= @Date1 and Date < = @Date2

---

Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

Answer 2

That error is an Oracle error, not a SQL Server error.  That indicates "Date" is not a valid date.  By casting @Date1 and @Date2 to a date, you are telling Oracle to implicitly cast "Date" to a date field and it is failing.

Answer 3

the colon's worked! thanks so much.