ReportServer Forum

TZM

Member

Registered: 2016-08-23

java.util.Date cannot be cast to java.sql.Timestamp

hi,

I am trying to deploy a Jasper report with "Date and Time" i/p params in reportserver. If i use Timestamp as the param data type in Jasper..i get this error. If i use java.Util.date in jasper..it works in RS but i cannot filter records between "Time" periods...I can do it only between days.

What is the i/p param data type that should be used to design the report...so that i can use both date and time correctly and easily.

PFB the error trace:

Error: An error occurred during report execution.
The report could not be executed: java.util.Date cannot be cast to java.sql.Timestamp
To get help please contact an administrator.
Details
net.datenwerke.rs.core.service.reportmanager.exceptions.ReportExecutorException: The report could not be executed: java.util.Date cannot be cast to java.sql.Timestamp at net.datenwerke.rs.core.service.reportmanager.ReportExecutorServiceImpl.execute(ReportExecutorServiceImpl.java:218) at net.datenwerke.rs.core.service.reportmanager.ReportExecutorServiceImpl.execute(ReportExecutorServiceImpl.java:119) at net.datenwerke.rs.core.server.reportexport.ReportExportServlet.exeucteReport(ReportExportServlet.java:535) at
.....

..

Thanks,

TZM

Thomas Davies

datenwerke

Registered: 2016-05-18

Re: java.util.Date cannot be cast to java.sql.Timestamp

Hi TZM,

java.util.Date should be the correct setting, but I can confirm that using this results in loosing the time component. It seems there is an implicit conversion from java.util.Date (which has a time component) to java.sql.Date (which hasn't).

I've raised a bug ticket to investigate this further.

In the meantime you can use this workaround:

Assuming your two parameters are param1 and param2, both defined as java.util.Date:
Create two additional parameters "from" and "to" of type java.sql.Timestamp.
Set them up as "not for prompting" and the following "Default value expression"
new java.sql.Timestamp($P{param1}.getTime())

in your query, now user $P{from} and $P{to} instead of param1 and param2:

select * from t_agg_order where $X{BETWEEN, OR_ORDERDATE, from, to}

Cheers,
Thomas

TZM

Member

Registered: 2016-08-23

Re: java.util.Date cannot be cast to java.sql.Timestamp

Hi Thomas,

Thanks for your reply. I had already tried your workaround...but without success.

I have two util.date params called startdate and enddate

Two non-prompting sql.timestamp params called startdate_sql and enddate_sql with

expression =  new java.sql.Timestamp($P{startdate}.getTime()) and similarly for enddate..

The where clause in my SQL query is...and $X{BETWEEN,cp.createtime,startdate_sql,enddate_sql}

The report has no output if i use a time span in the same day as my startdate and enddate. It doesnt seem to be casting date into timestamp properly.

However it works if i use 2 separate dates (and the results do not take into account the time values taken in by default...) and shows all results between the two dates.

(this is the case for both jasper studio and reportserver)

Please do revert soon,

Thanks and regards,

TZM

Thomas Davies

datenwerke

Registered: 2016-05-18

Re: java.util.Date cannot be cast to java.sql.Timestamp

Hi TZM,

that's strange. Maybe it also depends on the database? What are you using?

Please give this report a try (Doesn't use any table, so should work with any datasource - if not using mysql you might need to adapt the query and add e.g. a "from dual" or something similar). For me it prints:

value1              value1_sql 
2016-09-14          2016-09-14 11:00:32

<?xml version="1.0" encoding="UTF-8"?>
<!-- Created with Jaspersoft Studio version 6.2.0.final using JasperReports Library version 6.2.0  -->
<!-- 2016-09-14T11:16:10 -->
<jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="dtpt2" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="3e1b87f4-c503-4181-a803-ef954842944f">
	<property name="com.jaspersoft.studio.data.defaultdataadapter" value="mysql"/>
	<parameter name="param1" class="java.util.Date">
		<defaultValueExpression><![CDATA[new java.util.Date()]]></defaultValueExpression>
	</parameter>
	<parameter name="param1_sql" class="java.sql.Timestamp" isForPrompting="false">
		<defaultValueExpression><![CDATA[new java.sql.Timestamp($P{param1}.getTime())]]></defaultValueExpression>
	</parameter>
	<queryString>
		<![CDATA[select $P{param1} value1, $P{param1_sql} value1_sql]]>
	</queryString>
	<field name="value1" class="java.lang.String"/>
	<field name="value1_sql" class="java.lang.String"/>
	<columnHeader>
		<band height="61" splitType="Stretch">
			<staticText>
				<reportElement x="0" y="30" width="278" height="30" uuid="c9e4a568-3387-4b98-be1a-4e6f51a474dd"/>
				<textElement verticalAlignment="Bottom"/>
				<text><![CDATA[value1]]></text>
			</staticText>
			<staticText>
				<reportElement x="278" y="30" width="277" height="30" uuid="75a74477-32a9-475b-9051-0010f0116a92"/>
				<textElement verticalAlignment="Bottom"/>
				<text><![CDATA[value1_sql]]></text>
			</staticText>
		</band>
	</columnHeader>
	<detail>
		<band height="125" splitType="Stretch">
			<textField>
				<reportElement x="0" y="10" width="278" height="30" uuid="687499e6-5655-4a04-b49e-badc423f3bef"/>
				<textFieldExpression><![CDATA[$F{value1}]]></textFieldExpression>
			</textField>
			<textField>
				<reportElement x="278" y="10" width="277" height="30" uuid="b055acc9-bafc-4e99-9f1d-bf0854bd4996"/>
				<textFieldExpression><![CDATA[$F{value1_sql}]]></textFieldExpression>
			</textField>
		</band>
	</detail>
</jasperReport>

Cheers,
Thomas

TZM

Member

Registered: 2016-08-23

Re: java.util.Date cannot be cast to java.sql.Timestamp

Hi Thomas,

I am using PostgreSQL. Used your report. When i run it ..it says error executing SQL statement...and in the query editing window when i click on Read Fields i get the error 'Could not determine datatype of parameter $1'...

Am I doing something incorrectly ?

Really appreciate any help if possible .

Thanks,
TZM

Thomas Davies

datenwerke

Registered: 2016-05-18

Re: java.util.Date cannot be cast to java.sql.Timestamp

Hi TZM,

for postgres you have to change the query like this:

select $P{param1}::TIMESTAMP value1, $P{param1_sql}::TIMESTAMP value1_sql

for me this prints:

value1              value1_sql 
14.09.16 00:00      14.09.16 14:23

Cheers,
Thomas

TZM

Member

Registered: 2016-08-23

Re: java.util.Date cannot be cast to java.sql.Timestamp

Hi Thomas,

I modified your report and changed it to function the way I'm using it.. I'm directly displaying the parameters in the report (not using fields). Also I'm using a dummy query : " select 1". Note that the util date param is showing time as well....but the conversion to timestamp isnt working properly. I need this conversion to occur properly because my DB stores dates as timestamps and i need to query it with jasper + rs (which allows only util.date param)  ..

<?xml version="1.0" encoding="UTF-8"?>
<!-- Created with Jaspersoft Studio version 6.1.1.final using JasperReports Library version 6.1.1  -->
<!-- 2016-09-15T11:59:25 -->
<jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports [url=http://jasperreports.sourceforge.net/xsd/jasperreport.xsd]http://jasperreports.sourceforge.net/xsd/jasperreport.xsd"[/url] name="dtpt3" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="3e1b87f4-c503-4181-a803-ef954842944f">
	<property name="com.jaspersoft.studio.data.sql.tables" value=""/>
	<parameter name="param1" class="java.util.Date"/>
	<parameter name="param1_sql" class="java.sql.Timestamp" isForPrompting="false">
		<defaultValueExpression><![CDATA[new java.sql.Timestamp($P{param1}.getTime())]]></defaultValueExpression>
	</parameter>
	<queryString>
		<![CDATA[select 1]]>
	</queryString>
	<field name="?column?" class="java.lang.Integer"/>
	<columnHeader>
		<band height="61" splitType="Stretch">
			<staticText>
				<reportElement x="0" y="30" width="278" height="30" uuid="c9e4a568-3387-4b98-be1a-4e6f51a474dd"/>
				<textElement verticalAlignment="Bottom"/>
				<text><![CDATA[value1]]></text>
			</staticText>
			<staticText>
				<reportElement x="278" y="30" width="277" height="30" uuid="75a74477-32a9-475b-9051-0010f0116a92"/>
				<textElement verticalAlignment="Bottom"/>
				<text><![CDATA[value1_sql]]></text>
			</staticText>
		</band>
	</columnHeader>
	<detail>
		<band height="40" splitType="Stretch">
			<textField>
				<reportElement x="0" y="10" width="278" height="30" uuid="687499e6-5655-4a04-b49e-badc423f3bef"/>
				<textFieldExpression><![CDATA[$P{param1}]]></textFieldExpression>
			</textField>
			<textField>
				<reportElement x="278" y="10" width="277" height="30" uuid="b055acc9-bafc-4e99-9f1d-bf0854bd4996"/>
				<textFieldExpression><![CDATA[$P{param1_sql}]]></textFieldExpression>
			</textField>
		</band>
	</detail>
</jasperReport>

Thanks,

TZM

Thomas Davies

datenwerke

Registered: 2016-05-18

Re: java.util.Date cannot be cast to java.sql.Timestamp

Hi TZM,

doing it this way unfortunately defeats the purpose of the test. The truncation occurs when the parameter value is passed to the database, if you skip this part and display the parameter value directly the result means nothing. Sorry, maybe it wasn't clear what I was going to test.

I've updated the test report so you should be able to run it on postgres without modification.
You can download it here: https://www2.datenwerke.net/files/forum … tpt2.jrxml

Please run it with a parameter value for param1 of 2016-09-15 16:00 and post the output.

It should then print two lines. The first column contains the truncated date value as received by the database, the second column shows the "workaround-value".
Column three will - in the first line - read "2016-09-15 03:30" if it is between col1 and col2. The second row (with tsp = null) is only to be able to see the parameter-values no matter if the between condition is fulfilled or not.

Cheers,
Thomas

TZM

Member

Registered: 2016-08-23

Re: java.util.Date cannot be cast to java.sql.Timestamp

 	
value1	                value1_sql	             tsp	
15/9/16 12:00 AM	15/9/16 5:06 PM	        15/9/16 3:30 AM	
15/9/16 12:00 AM	15/9/16 5:06 PM		null	

Seems to be working fine ....but the way I am using these params is rather different (maybe incorrect too)..I use a util.date param with prompting as yes..( this is picked up naturally by RS too using Suggest Parameter)

..but in my query i pass the timestamp parameters with no prompting... and default values set to something like : new java.sql.Timestamp($P{dateparam}.getTime())

What seems to be happening is that the util date param is being passed to the db...not the sql timestamp ones...this is causing the query to run on dates with truncated times..

I am terribly confused ..

Your first response to my question was something i could understand properly and apply....could you perhaps provide an example based on that response..
apologies if im not making too much sense.. i am still learning how to use jasper/sql and rs properly...not great at any of these things..

Hi TZM,

java.util.Date should be the correct setting, but I can confirm that using this results in loosing the time component. It seems there is an implicit conversion from java.util.Date (which has a time component) to java.sql.Date (which hasn't).

I've raised a bug ticket to investigate this further.

In the meantime you can use this workaround:

Assuming your two parameters are param1 and param2, both defined as java.util.Date:
Create two additional parameters "from" and "to" of type java.sql.Timestamp.
Set them up as "not for prompting" and the following "Default value expression"
new java.sql.Timestamp($P{param1}.getTime())

in your query, now user $P{from} and $P{to} instead of param1 and param2:

select * from t_agg_order where $X{BETWEEN, OR_ORDERDATE, from, to}

Cheers,
Thomas

Thanks for your patience,

TZM

Thomas Davies

datenwerke

Registered: 2016-05-18

Re: java.util.Date cannot be cast to java.sql.Timestamp

Hi,

okay, maybe I got you confused with just using one parameter for from and to. Let's try again.

Take a look at this report: https://www2.datenwerke.net/files/forum … tpt4.jrxml

It has four parameters:
pFrom and pTo of class java.util.Date and with "Is for prompting" these are the parameters the user has to enter
pFrom_sql and pTo_sql of class java.sql.Timestamp and "not for prompting". These are used in the query and compute their value from what the user entered above parameters.

the report query looks like this

select i.tsp::TIMESTAMP from (
	select '2016-09-15 00:00' tsp union
	...
	select '2016-09-15 22:00' tsp union
	select '2016-09-15 23:00' tsp ) i
where  $X{BETWEEN, tsp, pFrom_sql, pTo_sql}

The inner query creates a dummy dataset of timestamps (this would normally be your table) - the outer query contains the where clause and uses the two _sql parameters.

Cheers,
Thomas

TZM

Member

Registered: 2016-08-23

Re: java.util.Date cannot be cast to java.sql.Timestamp

Perfect ! I finally got it. Thanks so much. .

I have two questions though..

1.) Earlier I used to pass my params ONLY in the where clause...never in my select statements...what's the significance of this ??...and in the previous example you've given..the syntax i have to follow is : 

$P{startdate}::TIMESTAMP 

(what exactly is the "::" doing here ? ..i noticed it doesnt work if i dont use the colons..

2.) In your latest example..in The Default Value Expressions..I understand the default value expression for the timestamp parameter...but i dont know why this has been used in the util.date param:

new java.util.Date(((new java.util.Date()).getTime())+(60*60*1000))

..i was keeping this blank initially..and it didnt work.

TZM

Member

Registered: 2016-08-23

Re: java.util.Date cannot be cast to java.sql.Timestamp

Also...as a normal practice..are parameters passed into the db as fields ? why cant we simply display parameters as they are ? for example..the report i had uploaded..because the parameter wasnt passed to the db..wasnt jasper "doing enough" to cast the date to timestamp...so that i could display $P{date_sql} values directly in my report... ??

eduardo

Administrator

Registered: 2016-11-01

Website

Re: java.util.Date cannot be cast to java.sql.Timestamp

Hi,

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