SSRS and Oracle dates
Hi, I am trying to run a query in Sql Reporting Services. The query runs perfect in Oracle but gives me errors while running it in the report. I have had a number of errors. First, day must be between 1 and end of month. Second, string must match. Third, not a valid month. It seems that the report wants to convert my where clause from '98-07-01' into another format, such as 01/09/1998. Is there any way around this? select * from vr_company_tran where reference_dt = '98-07-01' The reference_dt field is a date field. As I mentioned above, it works fine in SQL Developer. Thanks,
June 21st, 2011 7:56am

What is the Datasource Provider are you using?Rakesh M J Dont forget to mark it as Answered if found useful MCTS,MCITP,MCSS http://mycubeandreports.blogspot.com/
Free Windows Admin Tool Kit Click here and download it now
June 21st, 2011 8:10am

It is an Oracle datasource. Everything works fine until I introduce that reference_dt where clause, but I need this to be able to test and to eventually accept parameters.
June 21st, 2011 8:16am

Can you check with OLEDB for Oracle ?Rakesh M J Dont forget to mark it as Answered if found useful MCTS,MCITP,MCSS http://mycubeandreports.blogspot.com/
Free Windows Admin Tool Kit Click here and download it now
June 21st, 2011 8:45am

I wouldn't know how to configure that datasource. We are just using the Oracle datasource.
June 21st, 2011 8:53am

SELECT * FROM VR_COMPANY_REMITTANCE WHERE reference_dt = to_date('2009/05/01', 'yyyy/mm/dd') I guess this works.
Free Windows Admin Tool Kit Click here and download it now
June 21st, 2011 12:04pm

So I had a similar issue and could not find ANYTHING anywhere on the issue. Here is what I found out. The SSRS Calendar Control sends '3/3/2012 00:00:01 PM' into your Oracle query. In other words, although it looks like a simple date, it includes time and PM/AM. So if you pick March 29, 2012 on the calendar control at 8:20 AM, you are actually getting '3/29/2012 08:20:00 AM'. Depending on the date value in your Oracle database, you might have to change your query comparison.
March 29th, 2012 7:20pm

Well I tried to convert the time format above in Oracle and still cant get it to work! The problem I am/was having was that if I tried in my SQL to do the following To_Date(:DATE +1) to add 1 day to the value that BIDS sends to Oracle, I would get the ORA-01830: date format picture ends before converting entire input string Error. Eventually I had to do that bit in BIDS by adding 1 day to the date field by adding this to the date parameter like this: =DateAdd("d",1,Parameters!EndDate.Value) It seems that Oracle has a problem interpreting the date that BIDS is sending in.
Free Windows Admin Tool Kit Click here and download it now
November 1st, 2012 11:50am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics