jeevan.n Posted January 21, 2020 Share Posted January 21, 2020 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, Link to comment Share on other sites More sharing options...
yann22580 Posted January 21, 2020 Share Posted January 21, 2020 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 etchttps://www.helicaltech.com/date-parameters-in-jasper-server/https://community.jaspersoft.com/documentation/tibco-jaspersoft-studio-user-guide/v60/using-parameters-queriesRegards,Yann Link to comment Share on other sites More sharing options...
omar19863 Posted January 21, 2020 Share Posted January 21, 2020 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 Link to comment Share on other sites More sharing options...
luked Posted January 21, 2020 Share Posted January 21, 2020 I use the below for my date parameters in my SQL queriesAND 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 Link to comment Share on other sites More sharing options...
jeevan.n Posted January 28, 2020 Author Share Posted January 28, 2020 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 followingName: Parameter4Class: java.sql.TimestampDefault 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 belowSELECT *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:" Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now