1

While using the Microsoft SQL Server Report Builder, I am able to execute a query in the Query Designer and retreive the result. The query has a where clause on dates.

select * from table where DB_timestamp > to_date(:StartDate, 'mm/dd/yyyy') and DB_timestamp <  to_date(:EndDate, 'mm/dd/yyyy')

On execution, I get a pop up where I enter the two variables (StartDate & EndDate) in the given formats. Now when I try to run the same for generating a report, I have to select the two variables using a calender picker. Altough the dates show up in the desired format, I always get the below error:

An error occured during local report processing.
 Query execution failed for dataset 'XYZ'.
ORA-01843: not a valid month

What can cause the query to run successfully in one place but throw an error when the variables are selected by the calender picker?

Ahmed Faisal
  • 4,397
  • 12
  • 45
  • 74
  • what if you date and month are below 12? same error? is it possible to submit parameter as date rather than string? – vav May 01 '14 at 20:36
  • 1
    Perhaps culture settings are messing things up? (i.e. SSRS sending dd/mm/yyyy) – Jeroen May 01 '14 at 21:59
  • @Jeroen Thank you for the comment ... it helped me go in the right direction. If you post that as an answer, i'll gladly accept it :) – Ahmed Faisal May 02 '14 at 14:13
  • Glad I could help. If there were more relevant details be sure to edit my answer and add them; maybe it'll help others too. – Jeroen May 02 '14 at 14:44

2 Answers2

1

There mught be some kind of culture clash going on. If SSRS is sending dd/mm/yyyy and your db is expecting mm/dd/yyyy things will not work as expected or even crash on certain dates.

Jeroen
  • 60,696
  • 40
  • 206
  • 339
0

I would remove the to_date function from the where clause, e.g.

where DB_timestamp > :StartDate and ...

Mike Honey
  • 14,523
  • 1
  • 24
  • 40