How to use date time Parameter in the sql query editor

[jeevan.n]

Hi,

I have a report that is pulling records from a table and i have to limit the sql query to pull the records from only previous date. Am not sure how this can be done as am very new to the Jaspersoft Studio.

I have a "open_date" column in the select statement from the table which is returning the value e.g., in the format 8/22/19 9:23 AM [M/d/yy h:mm a] but i would like to limit the query result for only previous date in the where clause by using  a Parameter. Appreciate some advise here. 

Thanks,

 

[yann22580]

Hi,

I am not sure to understand what you mean by 'previous'. Anyway, I hope this can help you  :

relative date range parameter : MONTH-1, QUARTER , YEAR-2, DAY-7, WEEK etc

https://www.helicaltech.com/date-parameters-in-jasper-server/

https://community.jaspersoft.com/documentation/tibco-jaspersoft-studio-user-guide/v60/using-parameters-queries

Regards,

Yann

 

[omar19863]

You can do that from the query you would only add something like this (without the need to set a parameter):
WHERE date_format (date, '% Y-% m-% d) = DATE_ADD (CURDATE (), INTERVAL -1 DAY)

I just put that date format as an example you can put any other

[luked]

I use the below for my date parameters in my SQL queries

AND cast(open_date as date) BETWEEN to_date($P{Start Date YYYYMMDD}, 'YYYYMMDD') AND to_date($P{End Date YYYYMMDD}, 'YYYYMMDD')

Then you create the 2 parameters Start Date YYYYMMDD & End Date YYYYMMDD

[jeevan.n]

Thank you all for your quick support on the replies. I was able to make some headway but again having another issue while running the report. Any help is highly appreciated.

1. Created a new Parameter with the following

Name: Parameter4

Class: java.sql.Timestamp

Default Value Expression: new SimpleDateFormat("MM/dd/yy HH:mm a").format(new java.sql.Timestamp( System.currentTimeMillis() - 24 * 3600 * 1000))

2. And I called the Parameter in query like below

SELECT *
FROM casd."in"
WHERE 
open_date < $P!{Parameter4}
 

3. I get the following error message.

"net.sf.jasperreports.engine.JRException: net.sf.jasperreports.engine.JRException: Error preparing statement for executing the report query:"