Trimming time issue

ReportServer (EN)
1

Hi. When I pass datetime parameter to report, time is being trimmed and mysql executes query without time. Is it a bug or misconfiguration?

2

I think it is misconfiguration

3

Thanks for reply.
What is the right way to to do it? Every time I execute
select * from TableA where dateColumn1 between ${dateFrom} and ${dateTo}
I see in mysql logs that time is trimmed.
dateFrom and dateTo parameters are in “Date and Time” mode.

4

Hi nprokofiev,

we are investigating this issue. Please take a look here: https://forum.reportserver.net/viewtopic.php?id=631 and check if the workaround works for you.

Cheers,
Eduardo

5

For completeness:

I took a look at this issue and it seems the standard jasper behavior, not a reportserver bug.
Reportserver passes the parameter as java.util.Date to jasper. It passes the complete java.util.Date to jasper, including the time component. For checking this, you can write $P!{param1} in your query (instead of $P{param1}), and check what is arriving at the database. It should be something similar to: ‘Wed Mar 29 09:44:07 CEST 2017’.
Jasper seems to cut the time component when having a java.util.Date parameter in its query, take a look at http://community.jaspersoft.com/questio … -textfield for a similar issue. When having java.sql.Timestamp, this is not happening. So the “workaround” above is the correct solution.
Here the steps:

  1. Create a java.util.Date parameter “param1” with isForPrompting=true
  2. Create a java.sql.Timestamp parameter “param1_sql” with isForPrompting=false, and defaultValue=new java.sql.Timestamp($P{param1}.getTime())
  3. Use the “param1_sql” parameter in your query, e.g.: select * from mytable where myfield < $P{param1_sql}.
    The time component is not being cut anymore.

Cheers,
Eduardo