How do I set a parameter that is a date?

[mbmorgan]

In Jaspersoft Studio Professional...my SQL query contains a clause like "WHERE column_A >= $P{From_Date}", and my question is: how do I set the parameter 'From_Date'? 

Class:                   java.util.date

Is for Prompting:  checked

Default Value:     

new SimpleDateFormat("MM/dd/yyyy").parse("01/01/2001")

The column in question is of a Date datatype, in the MM/DD/YYYY format. 

[edit] - The problem comes when I try to read the fields from the database...I get "Error converting SQL statement into byte array..."

 

What am I doing wrong?  Any guidance is welcome.

 

Thanks,

M.B.Morgan

[bnee_1]

Hi M.B.,

Could you provide the full query?  The error might be from something else in the query.

I tested this query:

SELECT employee.last_name, employee.birth_date FROM employee WHERE employee.birth_date <= $P{paramDate}[/code]

which uses the sample foodmart db's employee table.  birth_date's data type is Date, and the parameter paramDate is of type java.util.Date with the exact same default expression you are using.

Another thing to check - in the Outline panel, under the Fields section, what is the Class for column_A?  It should be java.util.Date.

[mbmorgan]

The query also contains a CASE expression, which I'm starting to think is the real cause of the problem.  I posted another question about that on the community.  Do you know if a CASE expression is allowed in the query dialog? Thanks.

[ybablyuk]

Default value for Date paramented should be specify as:

new java.util.Date(97, 5, 1)[/code]

 

[mbmorgan]

Would you mind checking the query to see if the syntax is correct? I keep getting errors. This is a conditional query, set as a parameter (class java.lang.string), which is why I have it enclosed in quotes, but do I have quotes in the wrong place?

 

"select count(field_1), field_2 from table where field_2 = ...

and ProgStart >= '" + $P{From_Date} + "' and ProgStart <= '" + $P{To_Date} + "' and ...

group by field_2"

 

 

[ybablyuk]

Hi,

I can create report using query:

select count(orderid) as o_count, shippeddate from orderswhere shippeddate >=  $P{From_Date}  and shippeddate <= $P{To_Date} group by shippeddate[/code]

As example you can use my attached jrxml file. It's used sample sugarcrm database