Jump to content
Changes to the Jaspersoft community edition download ×

Datetime filter in ad hoc view doesn't work


guy.morgenstern

Recommended Posts

Hi,

I have created a simple domain that gives access to a table with a datetime field. My data source is MS SQL and the date time format is yyyy-mm-dd hh:mm:ss 

I am trying to create a simple ad hoc view using that domain and I put the datetime field in the column. When I try to create a filter for the datetime it is created but nothing happens. 

When looking at the logs I see exception.

 

What am I doing wrong? 

com.jaspersoft.commons.dataset.DataSetException: Exception calling JRDataSource.next() for query select  top 1000 "starttime"from (select endtime, entitytype, facility, entity, starttime as starttime from dbo.locationchangehistoryNID) "query"      where ("starttime" between {ts '2015-03-19 00:00:00'} and {ts '2015-03-19 00:00:00'})at com.jaspersoft.commons.semantic.dsimpl.JRQueryDataSet$JRDataSetIterator.next(JRQueryDataSet.java:417)at com.jaspersoft.commons.datarator.CachedData.fetchData(CachedData.java:150)... 127 moreCaused by: net.sf.jasperreports.engine.JRException: Error executing SQL statement for : nullat net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:240)at com.jaspersoft.jasperserver.api.engine.jasperreports.util.JRTimezoneJdbcQueryExecuter.createDatasource(JRTimezoneJdbcQueryExecuter.java:168)at com.jaspersoft.commons.util.JSControlledJdbcQueryExecuter.createDatasource(JSControlledJdbcQueryExecuter.java:113)at com.jaspersoft.commons.semantic.dsimpl.JRQueryDataSet.getJRDataSource(JRQueryDataSet.java:264)at com.jaspersoft.commons.semantic.dsimpl.JRQueryDataSet.access$100(JRQueryDataSet.java:58)at com.jaspersoft.commons.semantic.dsimpl.JRQueryDataSet$JRDataSetIterator.next(JRQueryDataSet.java:408)... 128 moreCaused by: java.sql.SQLDataException: [TIBCO]
[sqlServer JDBC Driver][sqlServer]The conversion of a varchar data type to a datetime data type resulted in an out-of-range value. at tibcosoftwareinc.jdbc.sqlserverbase.ddcw.b(Unknown Source) at tibcosoftwareinc.jdbc.sqlserverbase.ddcw.a(Unknown Source) at tibcosoftwareinc.jdbc.sqlserverbase.ddcv.b(Unknown Source) at tibcosoftwareinc.jdbc.sqlserverbase.ddcv.a(Unknown Source) at tibcosoftwareinc.jdbc.sqlserver.tds.ddr.v(Unknown Source) at tibcosoftwareinc.jdbc.sqlserver.tds.ddr.a(Unknown Source) at tibcosoftwareinc.jdbc.sqlserver.tds.ddq.a(Unknown Source) at tibcosoftwareinc.jdbc.sqlserver.tds.ddr.a(Unknown Source) at tibcosoftwareinc.jdbc.sqlserver.dda3.m(Unknown Source) at tibcosoftwareinc.jdbc.sqlserverbase.dde7.e(Unknown Source) at tibcosoftwareinc.jdbc.sqlserverbase.dde7.a(Unknown Source) at tibcosoftwareinc.jdbc.sqlserverbase.ddd2.a(Unknown Source) at tibcosoftwareinc.jdbc.sqlserverbase.dde7.h(Unknown Source) at tibcosoftwareinc.jdbc.sqlserverbase.dde7.u(Unknown Source) at tibcosoftwareinc.jdbc.sqlserverbase.ddd2.executeQuery(Unknown Source) at tibcosoftware.jdbc.common.PreparedStatementWrapper.executeQuery(PreparedStatementWrapper.java:45) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:92) at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:233) ... 133 more[/code]

Link to comment
Share on other sites

  • Replies 8
  • Created
  • Last Reply

Top Posters In This Topic

@guy.morgenstern  Re: I think that the problem relates to the fact that Jasper is using timestamp type and I am using datetime type in my MS SQL Server. 

I think you are right here, you can change the default domain type mappings by changing them in /WEB-INF/applicationContext-semanticLayer.xml search in that file for the bean called "jdbcMetaConfiguration" you will see that it has a property (jdbc2JavaTypeMapping) that maps the DB types to the Java Types that jasper will use, make sure that the mapping is correct, I have not seen this issue with MS SQL server but it happens some times with some Oracle data types (you will see some of them commented out in the file).

Hope this helps.. either way @djohnson53 is also in the right path, this may be something that can be changed at the Domain level.

Link to comment
Share on other sites

  • 5 months later...
  • 2 weeks later...

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 account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...