tafty

Many thanks for your assistance, got it working now.

Hi All thanks in advance for any help. I'm trying to construct a report which amongst other things uses a 'from' and 'to' date as part of the select. To do this I have created 2 parameters called 'date_from' & 'date_to' which are of class java.sql.Date and this give the end user a nice little calander icon to select the dates. I also have created 2 parameters called 'from_date' & 'to_date' which are are of class java.lang.String. The default vale expression is set to 'new SimpleDateFormat("dd/mm/yyyy").format($P{date_to}) (or date_from) These parameters are then embeded in the SQL as below ... and it.dstamp between to_date ($P{from_date}, 'dd/mm/yyyy') and to_date($P{to_date}, 'dd/mm/yyyy') ... In studio this works sometimes, and sometimes not.... When I display all the parameters to the report header, sometimes the string parameters show a date, and most of the time null. On the reports server, it never works. I have amended the jasper_config.properties file on the server to suit, I have added a suitable patten in the text field of the parameter, and I have repeatedly banged my head on my desk. Any assistance would be gretly appreciated. Many Thanks

Appologies if I'm talking nonsense or not giving enough information..... I have a report that amongst other things, calculates the date of the end of the week (Sunday) for a the field to_dstamp which is a date time field. The SQL to generate this below works correctly when run through Oracle SQL Developer select ir.invoice, to_char(ir.to_dstamp,'DAY','NLS_DATE_LANGUAGE=''numeric date language''') weekday, to_char(ir.to_dstamp, 'DD/MM/YYYY') as to_date, ir.to_dstamp, case to_char(ir.to_dstamp,'DAY','NLS_DATE_LANGUAGE=''numeric date language''') when '1' then to_char(ir.to_dstamp+6,'DD/MM/YYYY') when '2' then to_char(ir.to_dstamp+5, 'DD/MM/YYYY') when '3' then to_char(ir.to_dstamp+4,'DD/MM/YYYY') when '4' then to_char(ir.to_dstamp+3,'DD/MM/YYYY') when '5' then to_char(ir.to_dstamp+2,'DD/MM/YYYY') when '6' then to_char(ir.to_dstamp+1,'DD/MM/YYYY') else to_char(ir.to_dstamp, 'DD/MM/YYYY') end end_date, il.transaction_type from ab_invoice_results ir inner join ab_invoice_line il on ir.invoice = il.invoice where ir.approved_dstamp >= sysdate - 10 and ir.approved_dstamp <= sysdate and ir.status = 'Approved'order by ir.invoice, ir.client_id gives the result ....INV003973 7 22/03/2015 22-MAR-15 23.30.42.000000000 22/03/2015 SINV003974 7 29/03/2015 29-MAR-15 23.30.28.000000000 29/03/2015 SINV003975 7 05/04/2015 05-APR-15 23.30.38.000000000 05/04/2015 SINV003976 7 01/03/2015 01-MAR-15 23.30.35.000000000 01/03/2015 SINV003977 7 29/03/2015 29-MAR-15 23.30.38.000000000 29/03/2015 SINV003978 5 03/04/2015 03-APR-15 23.46.53.000000000 05/04/2015 UINV003979 7 05/04/2015 05-APR-15 23.51.46.000000000 05/04/2015 U.... note that invoice INV3976 with a datetime of '01-MAR-15 23.30.35.000000000' give an end date of '1/03/2015' which is correct as it is a Sunday. However, running the same result through ireports gives the end date of '08/03/2015'. This is because ireports is returning 'to_char(ir.to_dstamp, 'DD/MM/YYYY') as to_date' as '02/03/15' where to_date has a value of '01-MAR-15 23.30.35.000000000', so it is adding a day ? We have tried this in various versions of iReports including Jasper Studio. Most of the records / dates work correctly in iReports, but 4 records have added a day? We thought it might be due to the time ? but there are records with a datetime closer to midnight that worked correctly? Any help / ideas gratfully appreciated.