Passing Date / Time Prompt from iReport to SQL Server

[joepmo]

I am really struggling to wrap my hands around a query I am trying to execute in iReport 4.5, I cannot seem to get the right combination of options set in iReport to get the correct data set returned for my query.

Report Query is as follows

SELECT AccountNo, CustomerNo, ApplyToInvoiceNo, SUM(Amount) AS Balance,Min(EffectiveDate)as InvoiceDate
FROM ARDetail
WHERE EffectiveDate<=$P{CutoffDate}
GROUP BY AccountNo, CustomerNo, ApplyToInvoiceNo
HAVING SUM(Amount) <> 0

 

ISSUE

When prompted I enter  12/31/2011-- no time prompt included.  Yet when the report completes and and I print the prompt on the report it is showing a date of 12/30/2011 with a time of 22:30:00 and correspondingly it is excluding 12/31/2011 records.

 Somehow I think this date/time shift is related to the fact the under Tools-> Options->Compilation and Execution->Report Time Zone I have it set to Central Standard Time which is the time zone that I am in.    I am also not sure if my local time settings on my computer can affect the report.

******

Question 1 - What should my Report Time Zone be set to so that this date shift does not occur and it just uses the exact date I enter for the prompt?

Question 2 - Currently if I want to make a query against a SQL datetime field I have been using parameter field with a Parameter Class of java.util.date such as shown in the query above - $P{CutoffDate}.  The problem is that when the issue I described above occurs, I am not getting the correct records returned.  What I really would like is the ability to either always have the time for this date parameter default to 11:59 PM or have the ability to input a time with the parameter prompt.

Can this be done with the parameter class of java.util.date or do I need to change my parameter class?  As a less desirable alternative, can I modify my SQL query to accomplish this same goal, of course I assuming that the parameter does not shift the date to start with?

Sorry if this is a basic question, but I am somewhat confused because I can get the queries to run perfectly when run directly in the SQL Manager and I am just missing something in the iReport / JasperReport settings.

 

 

[butlerc]

Hi,

 

I found dates confusing when i first started, I created a different way of tranposing a date into a string to use in my queries.  We use this method for all out reports.

 

I have attached an example jrxml file, i always find it easier myself to copy what someone else has done rather than try to create it from an explaination.

I have 2 parameters,

End_Day - java.util.Date

End_DayToken - java.lang.String

The Token parameter takes the value from End_Day and converts it into a string, using a default expression:

"AND EffectiveDate < dateadd(dd, 1, '" + (new SimpleDateFormat("dd-MMM-yyyy")).format( $P{End_Day} ) + "')"

which would look something like this in your sql: AND EffectiveDate < dateadd(dd, 1,'01-Jan-2012')

I also included another parameter (that you don't need today) called Start_Day works in a similar way im sure you'll understand.