Why are dates coming across as strings?

[nealhudson]

Hi there,

 

I have a report which I have designed in iReports, over a MySQL database table containing an epoch time field. As part of the report, I have defined 2 java.util.Date parameters, to select a from and to date range. The SQL query contains the following where clause: WHERE l.time >= UNIX_TIMESTAMP($P{starttime})) AND l.time <= (UNIX_TIMESTAMP($P{endtime}))

When I run this query in MySQL Query browser, it runs fine if I enter a date in the format '2006-09-05 21:50'.

I have not entered any default values for the parameters, but selected the 'Use as a Prompt' option. In the JRXML file, the fields are defined as java.util.Date. When I run the report and select the dates, it pulls back records correctly.

 

 

On Jasperserver, I’ve set up a datatype called 'datetime', which is of type Date/Time. I have then set up a report based on the JRXML for the report outlined above, with the 2 input controls for the date range, using the newly created 'datetime' datatype for each and making them mandatory.

 

When I run the report in Jasperserver I am not getting any records back :(

 

I've turned on DEBUG level logging in log4j, and have noticed the following message which I think is what the problem is:

 

29 Aug 2007 10:37:38,237 DEBUG net.sf.jasperreports.engine.query.JRJdbcQueryExecuter ,http-8080-3:206 - Parameter #2 (starttime of type java.lang.String): Mon Aug 14 11:37:00 BST 2006

 

29 Aug 2007 10:37:38,239 DEBUG net.sf.jasperreports.engine.query.JRJdbcQueryExecuter ,http-8080-3:206 - Parameter #3 (endtime of type java.lang.String): Mon Aug 20 11:37:00 BST 2007

 

 

My question is - why are these defined as java.lang.String here and not java.util.Date?

 

 

Thanks for any help

Neal

[lucianc]

What JS version are you using? Did you set a timezone for the report data source?

 

Regards,

Lucian

[nealhudson]

Thanks Lucien,

 

I tried searching and came across this post:

http://www.jasperforge.org/index.php?option=com_joomlaboard&Itemid=215&func=view&catid=10&id=29495

 

It turns out that if I remove the Timezone setting from the datasource it works.

 

It's good to find the source of the problem, but annoying that it took so long - I've wasted over a day on this - I'm new enough to Jasper and thought it must have been something to do with the way the report or parameters were set up, not the datasource!

[csbac]

Hi!

As you might have seen in the other post, there is a patch out ... also, the 2.0.1 version of JS fixed it.

 

Sebastian