# Error while datetime query for jasperreport

**URL:** <https://forum.servoy.com/t/error-while-datetime-query-for-jasperreport/16207>\
**Category:** Classic Servoy\
**Created:** [July 16, 2012, 4:39pm UTC](https://forum.servoy.com/t/error-while-datetime-query-for-jasperreport/16207 "2012-07-16T16:39:43Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![tgs](https://avatars.discourse-cdn.com/v4/letter/t/f19dbf/32.png) [@tgs](https://forum.servoy.com/u/tgs)\
**Post date:** [July 16, 2012, 4:39pm UTC](https://forum.servoy.com/t/error-while-datetime-query-for-jasperreport/16207/1 "2012-07-16T16:39:43Z")

</div>

Hello,

I have a report SQL query in iReport by parameters [$P{DATE\_FROM} + $P{DATE\_TO}] for a PostgreSQL datetime column [offer\_date] in iReport by parameter:

```auto
SELECT	*
FROM	tbl_customer
FULL OUTER JOIN	tbl_offers	ON (tbl_offers.id_customer = tbl_customer.id_customer AND tbl_offers.offer_no = $P{OFFER_NO})
			OR (tbl_offers.id_customer = tbl_customer.id_customer AND tbl_offers.offer_date BETWEEN $P{DATE_FROM} AND $P{DATE_TO})
			OR (tbl_offers.id_customer = tbl_customer.id_customer AND $P{OFFER_NO} IS NULL AND $P{DATE_FROM} IS NULL AND $P{DATE_TO} IS NULL)
FULL OUTER JOIN tbl_offers_items ON tbl_offers_items.id_offer = tbl_offers.id_offer
WHERE	tbl_customer.id_customer = $P{CUSTOMER_ID}
ORDER BY	tbl_offers.offer_no;

```

I would like to get all related offers of the customer if the parameters [$P{OFFER\_NO} + $P{DATE\_FROM} + $P{DATE\_TO}] are NULL. Else it should return only an offer with the value of $P{OFFER\_NO} or the offers of the daterange between $P{DATE\_FROM} and $P{DATE\_TO}.

But I don’t get this working because of the parameter for the datetime column [offer\_date]. If I execute the query with real values in the SQL Editor in Servoy Dev. I get the results. Do I make a Preview in iReport and set the parameter values in the promp, I get errors in the output!?

What Parameter Class shuld the Parameters $P{DATE\_FROM} + $P{DATE\_TO} have?  
I tried with the property settings java.lang.String, java.util.Date and java.sql.Timestamp without success.  
How should I set the parameters in my Servoy method?

The date values come from form variables.

```auto
_reportParam.put("DATE_FROM",fv_date_from);
_reportParam.put("DATE_TO",fv_date_to);

```

I appreciate any little help.

Servoy 6.0.7  
iReport 4.6.0  
Servoy jasperreport plugin 4.0.0 b1

---

<div class="post-metadata">

**Author:** ![kwpsd](https://avatars.discourse-cdn.com/v4/letter/k/bb73d2/32.png) [@kwpsd](https://forum.servoy.com/u/kwpsd)\
**Post date:** [July 16, 2012, 5:39pm UTC](https://forum.servoy.com/t/error-while-datetime-query-for-jasperreport/16207/2 "2012-07-16T17:39:17Z")

</div>

Hi, Thomas.

Are you able to run the report in iReport ‘Preview’ mode (sans Servoy)?

You can get iReport to prompt you for the parameter input by selecting the parameter from the Report Inspector (left-side of display), then checking ‘Use as prompt’ in the parameter properties (right-side of display). Do this for all the paramters your SQL statement is expecting. Then, when you preview the report, iReport will prompt you to enter the parameters. This way, you can debug your report without using Servoy.

> What Parameter Class shuld the Parameters $P{DATE\_FROM} + $P{DATE\_TO} have?

I have used both ‘java.util.Date’ and ‘java.lang.String’ (for display only) classes in reports with no problem. Since you are using the parameters in the SQL statement, you should pass them as ‘java.util.Date’.

I hope this helps.

---

<div class="post-metadata">

**Author:** ![tgs](https://avatars.discourse-cdn.com/v4/letter/t/f19dbf/32.png) [@tgs](https://forum.servoy.com/u/tgs)\
**Post date:** [July 16, 2012, 6:14pm UTC](https://forum.servoy.com/t/error-while-datetime-query-for-jasperreport/16207/3 "2012-07-16T18:14:44Z")

</div>

Hi Kim,

thank you for your quick reply.

I have tried the iReport build in preview function with the parameters prompts.  
But if I enter e.g. 2012-05-10 for DATE\_FROM and 2012-05-15 for DATE\_TO as Class java.lang.string, I get this error:

```auto
Caused by: org.postgresql.util.PSQLException: ERROR: operator does not exist: timestamp without time zone >= character varying

```

Do I change the parameters Class to java.util.Date, the error output is:

```auto
Caused by: org.postgresql.util.PSQLException: ERROR: could not determine data type of parameter $5

```

---

<div class="post-metadata">

**Author:** ![kwpsd](https://avatars.discourse-cdn.com/v4/letter/k/bb73d2/32.png) [@kwpsd](https://forum.servoy.com/u/kwpsd)\
**Post date:** [July 16, 2012, 6:41pm UTC](https://forum.servoy.com/t/error-while-datetime-query-for-jasperreport/16207/4 "2012-07-16T18:41:10Z")

</div>

Both results are PostgreSQL exceptions, and I have limited experience with that database engine. Perhaps, one of the PostgreSQL experts would care to comment?

I am guessing that the first exception is due to an incompletely specified ‘datetime’ entry. You could try the ‘2012-05-15’ with variations of the time specification (e.g. ‘00:00:00.000’, etc.).

I am baffled by the second exception but, again, am guessing it is the manner in which the ‘datetime’ data is being entered. Here is a link that may help with that:

> **[8.5. Date/Time Types](https://www.postgresql.org/docs/8.0/datatype-datetime.html)**
>
> 8.5. Date/Time Types # 8.5.1. Date/Time Input 8.5.2. Date/Time Output 8.5.3. Time Zones 8.5.4. Interval Input 8.5.5. Interval Output PostgreSQL supports …

You could create a simple report with one ‘datetime’ parameter and experiment with it until you figure out what PostgreSQL is expecting.

---

<div class="post-metadata">

**Author:** ![ROCLASI](https://yyz2.discourse-cdn.com/flex010/user_avatar/forum.servoy.com/roclasi/32/4371_2.png) [@ROCLASI](https://forum.servoy.com/u/ROCLASI)\
**Post date:** [July 16, 2012, 8:30pm UTC](https://forum.servoy.com/t/error-while-datetime-query-for-jasperreport/16207/5 "2012-07-16T20:30:31Z")

</div>

```auto
Caused by: org.postgresql.util.PSQLException: ERROR: operator does not exist: timestamp without time zone >= character varying

```

Like in most cases the error says it all. You can’t use a timestamp in a condition against a varchar (character varying). It has no operator (\>=) to work with those 2 datatypes.  
If you want to pass a string you should cast it to a timestamp in SQL using the to\_timestamp() function. You can pass any string together with the mask it should use (very convenient!).  
See [http://www.postgresql.org/docs/9.1/stat … tting.html](http://www.postgresql.org/docs/9.1/static/functions-formatting.html) .

Hope this helps.

---

<div class="post-metadata">

**Author:** ![tgs](https://avatars.discourse-cdn.com/v4/letter/t/f19dbf/32.png) [@tgs](https://forum.servoy.com/u/tgs)\
**Post date:** [July 17, 2012, 7:58am UTC](https://forum.servoy.com/t/error-while-datetime-query-for-jasperreport/16207/6 "2012-07-17T07:58:37Z")

</div>

Hi Robert,

does this mean I should place the to\_timestamp() function into the SQL like

```auto
... AND tbl_offers.offer_date BETWEEN to_timestamp($P{DATE_FROM}) AND to_timestamp($P{DATE_TO})) ...

```

and set the parameters as java.lang.String like

```auto
DATE_FROM == '10.05.2012', 'dd.MM.yyyy'
DATE_TO == '15.05.2012', 'dd.MM.yyyy'

```

If yes, this doesn’t work (org.postgresql.util.PSQLException: ERROR: function to\_timestamp(character varying) does not exist)!

Regards,

---

<div class="post-metadata">

**Author:** ![omar](https://avatars.discourse-cdn.com/v4/letter/o/f0a364/32.png) [@omar](https://forum.servoy.com/u/omar)\
**Post date:** [July 17, 2012, 12:00pm UTC](https://forum.servoy.com/t/error-while-datetime-query-for-jasperreport/16207/7 "2012-07-17T12:00:03Z")

</div>

Hi Thomas,

PostgreSQL is expecting two strings to\_timestamp(text, text) and is receiving to\_timestamp(character varying) so you will have to pass in the two strings as two arguments seperated by a comma. Also be careful when converting dates to timestamp because selecting a daterange with timestamps can be tricky. I prefer to convert the datetime to a date and compare with that.

Kind regards,

---

<div class="post-metadata">

**Author:** ![tgs](https://avatars.discourse-cdn.com/v4/letter/t/f19dbf/32.png) [@tgs](https://forum.servoy.com/u/tgs)\
**Post date:** [July 17, 2012, 12:01pm UTC](https://forum.servoy.com/t/error-while-datetime-query-for-jasperreport/16207/8 "2012-07-17T12:01:26Z")

</div>

Now I have set the PSQL column “offer\_date” to data type “date” and the iReport Parameters to the Class “java.util.Date”.

If I set the parameter by

```auto
_reportParam.put("DATE_FROM",utils.dateFormat(fv_offer_date_from_dat,'dd.MM.yyyy'));

```

I get the error

```auto
<< java.lang.Exception: java.lang.String cannot be cast to java.util.Date >>

```

Does anybody understand what is wrong?

Regards,

---

<div class="post-metadata">

**Author:** ![Thomas\_Parry](https://avatars.discourse-cdn.com/v4/letter/t/b38774/32.png) [@Thomas\_Parry](https://forum.servoy.com/u/Thomas_Parry)\
**Post date:** [July 19, 2012, 3:14pm UTC](https://forum.servoy.com/t/error-while-datetime-query-for-jasperreport/16207/9 "2012-07-19T15:14:36Z")

</div>

Look at my reply on Servoy Forge.  
[https://www.servoyforge.net/boards/13/topics/561](https://www.servoyforge.net/boards/13/topics/561)