none
Passing Date Parameter to Oracle Query

    Question

  • ActivityDate is a report parameter set up as a date that I'm trying to pass into an Oracle query. The specific WHERE clause is

    WHERE PROJ_DATE = TO_DATE(:ActivityDate,'YYYY-MM-DD')

    When I run the query from the Data tab, all works as expected. I suspect the reason is that I under the date as 2005-12-31. If I enter the date as 12/31/05, the query fails ("Not a valid month") unless I change to function's format to 'MM/DD/YYYY' in which case I, again, get good results.

    But when I run the report from Preview, I get no results at all no matter what format I use in the function.

    Any chance any of you have seen this and know how to work with it?

    Wednesday, August 16, 2006 6:07 PM

Answers

  •  

    This thread is really old, but I'll reply anyways.

     

    Here's the answer:

    WHERE PROJ_DATE = :ActivityDate

     

    For an explanation, keep reading...

     

    As you probably well know, there is a difference between a query parameter and a report parameter. The primary use for report parameters is to pass user-entered values to query parameters, so it makes sense to start with the report parameter first.

     

    You want your users to be able to use the DatePicker control, so your report parameter needs to have a DateTime data type. Parameter name should be ActivityDate.

     

    Next, and this might have been done automatically already, you need to create/link the query parameter to the report parameter. Go to the data tab, select your dataset, click the ellipses (...) button to open properties. Go to the Parameters tab. Make sure that you have a parameter named :ActivityDate with a value of "=Parameters!ActivityDate.Value" (minus the quotes). Since it appears you are using the .NET Framework Data Provider for Oracle, you can use a "named" parameter like this. If you are using ODBC, you have to use the ? placeholder, and they have to be listed in the order that they appear in the query.

     

    You'll notice that there is no place to specify the data type for a query parameter. This means that your query has to be written to handle the "value" you specified in the Parameters tab. Because your report parameter is a DateTime, it gets passed to the query as a DateTime. There is no need to use the TO_DATE function because it is already a date. This is great for you because your datatypes match:

     

    WHERE PROJ_DATE = :ActivityDate

     

    If for some reason, PROJ_DATE was a string in the database (like "20071010"), you would have 2 options.

     

    1.) WHERE TO_DATE(PROJ_DATE,'YYYYMMDD') = :ActivityDate

    2.) Still leave the WHERE clause as "WHERE PROJ_DATE = :ActivityDate" and change the "value" of your query parameter to a VB expression that will reformat your DateTime value to the appropriate char string.

     

    There were several responses to this that involved converting a DateTime to a String and then back to a DateTime again. This is rarely necessary, but one such occasion comes to mind...

     

    This tip is actually VERY important to understand. When you use the DatePicker control, the actual selected value includes the time. You don't see the time, but in reality the value is 10/17/2007 00:00:00. If by chance your database field contains times as well, YOU NEED TO READ THIS.

     

    If you do not truncate the time-portion off your database field in the WHERE clause, you will exclude records that you don't want to exclude. Example:

     

    SQL:

    Code Block

    WHERE EVENT_DATETIME <= :dateParm

     

     

     

    Hypothetical values:

    WHERE (10/17/2007 09:49:42) <= (10/17/2007 00:00:00)

     

    If you wanted to include records from 10/17, you would not get this one. In fact, you would only get records that happened to occur EXACTLY at 00:00:00.

     

    The solution is to TRUNC the DateTime field like this:

     

    SQL:

    Code Block
    WHERE TRUNC(EVENT_DATETIME) <= :dateParm

     

     

     

    Hypothetical values:

    WHERE (10/17/2007 00:00:00) <= (10/17/2007 00:00:00)

     

    I hope that helps fill in some of the missing gaps regarding parameters...

     

    -Josh

    Wednesday, October 17, 2007 7:45 PM

All replies

  • The question is what is the data type of the report parameter specified as? Try it as a string instead of a date.
    Wednesday, August 16, 2006 11:28 PM
  • If I type it as string, then the user has to key in the date rather than use the calendar pop-up. I don't want to force that.
    Wednesday, August 16, 2006 11:43 PM
  • Could someone please take a look at this again. Although this question is old, I'm still stuck for an answer.

    I think what I need to know is this: If I set a report parameter's data type to DateTime, what is the format of the returned value? As mentioned in the first entry of this thread, what I need to do is:

    WHERE PROJ_DATE = TO_DATE(:ActivityDate,'YYYY-MM-DD')

    'YYYY-MM-DD' (and many other formats I've tried) doesn't work. What's right?

    Wednesday, December 13, 2006 7:01 PM
  • Hi,

    Did you ever find a solution to this problem?

    Cheers

    Phil
    Monday, June 11, 2007 3:11 AM
  • No, I'm afraid I never did. I've been running the report manually until I can take the time to revisit the problem.
    Thursday, June 14, 2007 8:17 PM
  •  

    Use this syntax: WHERE PROJ_DATE = TO_DATE(TO_CHAR(:ActivityDate), 'DD-MON-YY')

    Wednesday, July 18, 2007 6:02 PM
  • Use this syntax to omit time portion:

    WHERE TO_DATE(PROJ_DATE), 'DD-MON-YY') = TO_DATE(TO_CHAR(:ActivityDate), 'DD-MON-YY')

    Wednesday, July 18, 2007 6:17 PM
  •  

    This thread is really old, but I'll reply anyways.

     

    Here's the answer:

    WHERE PROJ_DATE = :ActivityDate

     

    For an explanation, keep reading...

     

    As you probably well know, there is a difference between a query parameter and a report parameter. The primary use for report parameters is to pass user-entered values to query parameters, so it makes sense to start with the report parameter first.

     

    You want your users to be able to use the DatePicker control, so your report parameter needs to have a DateTime data type. Parameter name should be ActivityDate.

     

    Next, and this might have been done automatically already, you need to create/link the query parameter to the report parameter. Go to the data tab, select your dataset, click the ellipses (...) button to open properties. Go to the Parameters tab. Make sure that you have a parameter named :ActivityDate with a value of "=Parameters!ActivityDate.Value" (minus the quotes). Since it appears you are using the .NET Framework Data Provider for Oracle, you can use a "named" parameter like this. If you are using ODBC, you have to use the ? placeholder, and they have to be listed in the order that they appear in the query.

     

    You'll notice that there is no place to specify the data type for a query parameter. This means that your query has to be written to handle the "value" you specified in the Parameters tab. Because your report parameter is a DateTime, it gets passed to the query as a DateTime. There is no need to use the TO_DATE function because it is already a date. This is great for you because your datatypes match:

     

    WHERE PROJ_DATE = :ActivityDate

     

    If for some reason, PROJ_DATE was a string in the database (like "20071010"), you would have 2 options.

     

    1.) WHERE TO_DATE(PROJ_DATE,'YYYYMMDD') = :ActivityDate

    2.) Still leave the WHERE clause as "WHERE PROJ_DATE = :ActivityDate" and change the "value" of your query parameter to a VB expression that will reformat your DateTime value to the appropriate char string.

     

    There were several responses to this that involved converting a DateTime to a String and then back to a DateTime again. This is rarely necessary, but one such occasion comes to mind...

     

    This tip is actually VERY important to understand. When you use the DatePicker control, the actual selected value includes the time. You don't see the time, but in reality the value is 10/17/2007 00:00:00. If by chance your database field contains times as well, YOU NEED TO READ THIS.

     

    If you do not truncate the time-portion off your database field in the WHERE clause, you will exclude records that you don't want to exclude. Example:

     

    SQL:

    Code Block

    WHERE EVENT_DATETIME <= :dateParm

     

     

     

    Hypothetical values:

    WHERE (10/17/2007 09:49:42) <= (10/17/2007 00:00:00)

     

    If you wanted to include records from 10/17, you would not get this one. In fact, you would only get records that happened to occur EXACTLY at 00:00:00.

     

    The solution is to TRUNC the DateTime field like this:

     

    SQL:

    Code Block
    WHERE TRUNC(EVENT_DATETIME) <= :dateParm

     

     

     

    Hypothetical values:

    WHERE (10/17/2007 00:00:00) <= (10/17/2007 00:00:00)

     

    I hope that helps fill in some of the missing gaps regarding parameters...

     

    -Josh

    Wednesday, October 17, 2007 7:45 PM
  • Thank you. There is a annoying difference between SQL and Oracle. I was using SSRS and SQL report builder and converting to oracle BI.

    Friday, March 27, 2009 4:02 PM
  • I concur with the post by bell.joshua and would say that the answer is

    WHERE PROJ_DATE = :ActivityDate

    I would also add however that it is important that you use an "Oracle OLE  Provider for Oracle" when setting up your datasource. I tried using the "Microsoft OLE provider for Oracle" with the above answer and had no luck, but the native Oracle one did work.
    Wednesday, November 11, 2009 10:13 AM