Problem with Parameter type Date

0
Hi all.

I tell you about my present problem with JI:
Now I can run reports made with iReport in JI. No problem except in reports that have a prompt parameter type date. In iReport I could only obtain this with parameters type String, although they were really dates. In iReport all works fine.
But in JI, the Input Controls (date Parameters type String in iReport) doesn't work properly. I´ve changed and create Input Controls type Date and type String, with no results.

Any solution?

Thanks in advance
rafa's picture
17
Joined: Sep 12 2006 - 9:20pm
Last seen: 12 years 9 months ago

33 Answers:

0
I also have a parameter date type question too. i was trying to read iReport from JasperServer. everything was fine, but when i tried to do "add control" on JasperServer, it gave me error messages said the date type are not compatible, so i changed the parameter's date type in ireport to java.until.date. but still gave me the error message said: sql error.
anybody knows how to solve this problem?
thanks a lot.
kelly's picture
38
Joined: Jun 26 2007 - 4:12am
Last seen: 11 years 11 months ago
0
It's easiest to use a String to compare the dates in SQL through JasperServer/Reports.

Define some String parameters that you're only going to use in the query E.g.
Code:
$P{QUERY_X_DATE}</td></tr></tbody></table>, then use the simple date formatter to set the default value using the original passed-in date as a source.<br />
<br />
The Default field for the QUERY_X_DATE parameter should look like this:<br />
<br />
<table align="center" border="0" cellpadding="3" cellspacing="1" width="90%"><tbody><tr><td><b>Code:</b></td></tr><tr><td><pre>(new SimpleDateFormat("yyyy-MM-dd"«»)).format($P{X_DATE})</td></tr></tbody></table><br />
<br />
Then you can use <table align="center" border="0" cellpadding="3" cellspacing="1" width="90%"><tbody><tr><td><b>Code:</b></td></tr><tr><td><pre>SQL_COLUMN = $P{QUERY_X_DATE}</td></tr></tbody></table> for comparison in the SQL.<br />
<br />
Cheers<br />
<br />
C<br />
<br />
Post edited by: chrish, at: 2007/07/13 19:24<br>Post edited by: chrish, at: 2007/07/13 19:24
chrish's picture
9
Joined: Jun 20 2007 - 2:24am
Last seen: 12 years 1 day ago
0
You are RIGHT. THANKS A LOT. :laugh:
kelly's picture
38
Joined: Jun 26 2007 - 4:12am
Last seen: 11 years 11 months ago
0
Hi,

I have the same problem and trying your solution the report works well in iReport, but published in JasperServer doesn't work anymore...

Looking on the log file I noticed the string parameter (the one used for the comparison) results empty. If I take it off from the WHERE condition and try to show the value inside the report .. i see it is correctly set.

How can I do? There's a method to force the computation of a parameter during the query?

Thanks a lot!
Massimo
mvisman's picture
Joined: Feb 5 2007 - 4:25pm
Last seen: 12 years 4 months ago
0
Hmmm, not sure why it wouldn't work. Here's a little more detail.

You need to create a JasperServer date type input control with the name (from my example) X_DATE.

This will then pass in the value that QUERY_X_DATE uses to create the formatted String.

Sounds like JasperServer isn't passing through the date parameter so you may have the input control name incorrect (it has to match the parameter name).

N.B. Using the 'Use as a Prompt' option in iReport will not work in JasperServer.

Regards

Chris
chrish's picture
9
Joined: Jun 20 2007 - 2:24am
Last seen: 12 years 1 day ago
0
Hello, I have the same problem. When I found this example, I change my code ( just follow the example ). The good news is, no any exception happens. But, the bad news is, I always get Empty Report. :blink: I try to figure out what happen, but there is no any exception on server. I have no clues. I search the whole forum and read most of the related articles. Someone has the same problems with mine ( Empty Report ) but no one gets the feedback. :blush:

I'll very appreicate that if anyone can help me or give me any suggestion.

My environment:
Oracle 9
jasperserver-2.0-bin
jdk 1.5

Thanks a lot.
:blush:
ali88's picture
11
Joined: Jul 27 2007 - 6:02pm
Last seen: 11 years 10 months ago
0
I'm using JasperServer 1.2 but I don't think that should make a difference.


Are you using the date in a '<=' SQL expression?


If so you'll need to add ' 23:59:59' to the end of the SimplDateFormat to make it
Code:
(new SimpleDateFormat("yyyy-MM-dd 23:59:59"«»)).format($P{X_DATE})</td></tr></tbody></table><br />
<br />
<br />
The X in my parameter names should be replaced with whatever you've called your parameters.<br />
<br />
<br />
The most important thing is to make sure all your input controls and parameters are named and referenced correctly.<br />
<br />
<br />
Other than that, you could try running the SQL directly against the DB, to see what's happening.<br />
<br />
C
chrish's picture
9
Joined: Jun 20 2007 - 2:24am
Last seen: 12 years 1 day ago
0
Hello, chrish. Thanks for your kindly reply.

Unfortunatly, I think I need to solve my another problem first.....:blush:

I want to have my date format looks like 'dd/MM/yyyy'. So I follow the guide and then change the setting in the jasperserver_config.properties:

Code:
<br />
date.format=dd/MM/yyyy<br />
datetime.format=dd/MM/yyyy HH:mm<br />
</td></tr></tbody></table><br />
<br />
On my jasperserver, I choose en_US as Locale and CET as TimeZone.<br />
<br />
Now, the problem is,<br />
everytime I choose the date from calendar, e.g. Dec-01-2007, it will become 01/12/07 (What I expected is 01/12/2007).<br />
<br />
Could you give me a hint or suggestion that anything I missing? Thank you. :blush:
ali88's picture
11
Joined: Jul 27 2007 - 6:02pm
Last seen: 11 years 10 months ago
0
ali88 wrote:
Now, the problem is,
everytime I choose the date from calendar, e.g. Dec-01-2007, it will become 01/12/07 (What I expected is 01/12/2007).


How does calendar.date.format look like? It should be something like %d/%m/%Y (note the uppercase Y).

HTH,
Lucian
lucianc's picture
7055
Joined: Jul 17 2006 - 1:10am
Last seen: 2 weeks 6 days ago
0
Hi, Lucian. Thanks for your reply.
Yes, my setting is

Code:
<br />
date.format=dd/MM/yyyy<br />
datetime.format=dd/MM/yyyy HH:mm<br />
calendar.date.format=%d/%m/%Y<br />
calendar.datetime.format=%d/%m/%Y %H:%M<br />
</td></tr></tbody></table><br />
<br />
I use JBoss 4.0.5<br />
<br />
I'm wordering that is it possible the properties files are not applied after my deployment? :dry: <br />
<br />
Thank you.
ali88's picture
11
Joined: Jul 27 2007 - 6:02pm
Last seen: 11 years 10 months ago
0
ali88 wrote:
I'm wordering that is it possible the properties files are not applied after my deployment?


I couldn't know about that, you'll have to check it yourself: navigate to a page that includes a calendar, view the source page and search for "Calendar.setup" to see what pattern it uses.

Regards,
Lucian
lucianc's picture
7055
Joined: Jul 17 2006 - 1:10am
Last seen: 2 weeks 6 days ago
0
Thanks, Lucian. Now my first problem is sovled. :laugh:

Even I solved my first problem, I still have date format issues on my report.

I have 2 issues:

1)I transfer date format from "EEE MMM dd hh:mm:ss 'CET' yyyy" to "DD/MM/YYYY" in report. Sometimes it works but sometimes doesn't. Because sometimes it causes an exception like "Unparseable date: "Tue Aug 23 00:00:00 CEST 2005". But if I change to CEST, sometimes the exception will be "Unparseable date: "Tue Aug 23 00:00:00 CET 2005"


2)If the issue 1) did not happen, I can run my report without any exception. But I always got incorrect report.


What I do is:
1.(iReport)Create a simple jrxml with -
a. one field: $F{COL_1}
b. two parameters: $P{DATE_FROM} and $P{SDATE_FROM}
$P{DATE_FROM} with java.util.Date
$P{SDATE_FROM} with java.lang.String

For $P{SDATE_FROM}, I have Default value expression -
Code:
<br />
($P{DATE_FROM}==null?"01/01/2005":«»(new SimpleDateFormat("dd/MM/yyyy"«»)).format(( new SimpleDateFormat("EEE MMM dd hh:mm:«»ss 'CET' yyyy"«»)).parse($P{DATE_FROM}.toString())))<br />
</td></tr></tbody></table><br />
<br />
2.(jasperserver)Create a Query -<br />
<table align="center" border="0" cellpadding="3" cellspacing="1" width="90%"><tbody><tr><td><b>Code:</b></td></tr><tr><td><pre><br />
SELECT COL_1 FROM myTable<br />
WHERE myDate BETWEEN <br />
to_date($P{SDATE_FROM},'DD/MM/YYYY')<br />
AND<br />
to_date('31/12/2007','DD/MM/YYYY')<br />
</td></tr></tbody></table><br />
<br />
3.(jasperserver)Create a input control -<br />
DATE_FROM with DataType Date<br />
<br />
4.(jasperserver)Create a jasperreport -<br />
this report use jrxml from 1., query from 2., and  one input from 3.<br />
<br />
Finally, I run my report and always get the issues.<br />
<br />
Thanks for any help for me. :blush:
ali88's picture
11
Joined: Jul 27 2007 - 6:02pm
Last seen: 11 years 10 months ago
0
A thing that looks suspicious:
new SimpleDateFormat("dd/MM/yyyy")).format(( new SimpleDateFormat("EEE MMM dd hh:mm:ss 'CET' yyyy")).parse($P{DATE_FROM}.toString())


Is this really necessary? Wouldn't the following suffice?
Code:
SimpleDateFormat("dd/MM/yyyy"«»)).format($P{DATE_FROM})</td></tr></tbody></table><br />
<br />
Regarding the "incorrect report" issue, please enable some logging (at JasperReports level or at DB level) to see exactly what value is used for the parameter so that you can clearly determine why this happens.<br />
<br />
Regards,<br />
Lucian
lucianc's picture
7055
Joined: Jul 17 2006 - 1:10am
Last seen: 2 weeks 6 days ago
0
Thanks, Lucian.
Yes, I feel strange, too. Because I wrote the code like yours at first, but it always got exception: ORA-... non-numberic ....It seens like when web-page submit $P{DATE_FROM} to server, it didn't like what we saw (e.g. I choose '01/12/2007' and then submit, I suppose that it will be '01/12/2007', but actually....no)

That's why I use SimpleDateFormat to parse the date....but....it doesn't work. :unsure:
ali88's picture
11
Joined: Jul 27 2007 - 6:02pm
Last seen: 11 years 10 months ago
0
$P{DATE_FROM} is a java.util.Date instance and not a String, so I understand that you need to format it if you want to use it as a String (not sure why directly using it as a data wouldn't work, though).

What I don't understand at all is why you need to do new SimpleDateFormat("EEE MMM dd hh:mms 'CET' yyyy")).parse($P{DATE_FROM}.toString()) instead of simply using $P{DATE_FROM}. You are representing the java.util.Date value as a String, and then parsing back (or at least trying to) the String into a java.util.Date. Am I missing something here?
lucianc's picture
7055
Joined: Jul 17 2006 - 1:10am
Last seen: 2 weeks 6 days ago
0
Hello again,

Sounds like you're getting confused with your date formats and formatting.


java.util.Date doesn't have a format, it's just a number of seconds since the epoc (as far as I remember). If you output a java.util.Date object on your report it will default to your local date time format. This is what your $P{DATE_FROM}.toString() is doing.


If you want to apply a specific format to a Date object (for display in your report) you can use the pattern parameter of the text field to get the correct format. This means you don't have to worry about which date/time format the server you deploy to is using.


The SimpleDateFormat solution I posted was for querying database date fields using a string representation of a java.util.Date as I couldn't easily cast it to a java.sql.Date.


In Oracle you will probably have to use to_date function in a slightly different way, inlining the parameter rather than using it as a PreparedStatement parameter.


Try:
Code:
to_date('$P!{SDATE_FROM}','DD/MM/YYYY')</td></tr></tbody></table><br />
<br />
<br />
As Lucian said, your complicating the matter if you use <table align="center" border="0" cellpadding="3" cellspacing="1" width="90%"><tbody><tr><td><b>Code:</b></td></tr><tr><td><pre>($P{DATE_FROM}==null?"01/01/2005":«»(new SimpleDateFormat("dd/MM/yyyy"«»)).format(( new SimpleDateFormat("EEE MMM dd hh:mm:«»ss 'CET' yyyy"«»)).parse($P{DATE_FROM}.toString())))</td></tr></tbody></table><br />
<br />
You're changing the numeric Date into a String and then changing it back to a Date and then back to a String.<br />
<br />
<br />
The other issue is that if you deploy to a server in another local it will break your code.  You are relying on $P{DATE_FROM}.toString() to output in a specific format.<br />
<br />
<br />
All you need is this in your default value for $P{SDATE_FROM}:<br />
<table align="center" border="0" cellpadding="3" cellspacing="1" width="90%"><tbody><tr><td><b>Code:</b></td></tr><tr><td><pre>($P{DATE_FROM}==null?"01/01/2005":«»(new SimpleDateFormat("dd/MM/yyyy"«»)).format($P{DATE_FROM}))</td></tr></tbody></table><br />
<br />
<br />
Sorry, have to ask a silly question.  How are you running the report?  From iReport toolbar, the JasperServer panel or from the JasperServer web interface?<br />
<br />
<br />
Let me know if any of this helps.<br />
<br />
<br />
Cheers<br />
<br />
<br />
C<br />
<br />
<br />
N.B. If you want to include the last day of the month you will have to add hours to your to date.  This will have to be: <table align="center" border="0" cellpadding="3" cellspacing="1" width="90%"><tbody><tr><td><b>Code:</b></td></tr><tr><td><pre>to_date('31/12/2007 23:59:59','DD/MM/YYYY HH24:MI:«»SS')</td></tr></tbody></table>
chrish's picture
9
Joined: Jun 20 2007 - 2:24am
Last seen: 12 years 1 day ago
0
Hello again,

Sounds like you're getting confused with your date formats and formatting.


java.util.Date doesn't have a format, it's just a number of seconds since the epoc (as far as I remember). If you output a java.util.Date object on your report it will default to your local date time format. This is what your $P{DATE_FROM}.toString() is doing.


If you want to apply a specific format to a Date object (for display in your report) you can use the pattern parameter of the text field to get the correct format. This means you don't have to worry about which date/time format the server you deploy to is using.


The SimpleDateFormat solution I posted was for querying database date fields using a string representation of a java.util.Date as I couldn't easily cast it to a java.sql.Date.


In Oracle you will probably have to use to_date function in a slightly different way, inlining the parameter rather than using it as a PreparedStatement parameter.


Try:
Code:
to_date('$P!{SDATE_FROM}','DD/MM/YYYY')</td></tr></tbody></table><br />
<br />
<br />
As Lucian said, your complicating the matter if you use <table align="center" border="0" cellpadding="3" cellspacing="1" width="90%"><tbody><tr><td><b>Code:</b></td></tr><tr><td><pre>($P{DATE_FROM}==null?"01/01/2005":«»(new SimpleDateFormat("dd/MM/yyyy"«»)).format(( new SimpleDateFormat("EEE MMM dd hh:mm:«»ss 'CET' yyyy"«»)).parse($P{DATE_FROM}.toString())))</td></tr></tbody></table><br />
<br />
You're changing the numeric Date into a String and then changing it back to a Date and then back to a String.<br />
<br />
<br />
The other issue is that if you deploy to a server in another local it will break your code.  You are relying on $P{DATE_FROM}.toString() to output in a specific format.<br />
<br />
<br />
All you need is this in your default value for $P{SDATE_FROM}:<br />
<table align="center" border="0" cellpadding="3" cellspacing="1" width="90%"><tbody><tr><td><b>Code:</b></td></tr><tr><td><pre>($P{DATE_FROM}==null?"01/01/2005":«»(new SimpleDateFormat("dd/MM/yyyy"«»)).format($P{DATE_FROM}))</td></tr></tbody></table><br />
<br />
<br />
Sorry, have to ask a silly question.  How are you running the report?  From iReport toolbar, the JasperServer panel or from the JasperServer web interface?<br />
<br />
<br />
Let me know if any of this helps.<br />
<br />
<br />
Cheers<br />
<br />
<br />
C<br />
<br />
<br />
N.B. If you want to include the last day of the month you will have to add hours to your to date.  This will have to be: <table align="center" border="0" cellpadding="3" cellspacing="1" width="90%"><tbody><tr><td><b>Code:</b></td></tr><tr><td><pre>to_date('31/12/2007 23:59:59','DD/MM/YYYY HH24:MI:«»SS')</td></tr></tbody></table>
chrish's picture
9
Joined: Jun 20 2007 - 2:24am
Last seen: 12 years 1 day ago
0
Thank you two, Lucian and Chrish. :)

I run from JasperServer web interface.

I think maybe I write/think too much....just beacuse I try too many ways. Let me get back to my beginning.

I create 2 input control in JasperSerevr, with DataType as Date.


In iReport, I add 4 parameters, 2($P{D1} and $P{D2}) as java.util.Date and they are for input control. The other 2($P{S1} and $P{S2}) are java.lang.String, I will use them for Oracle SQL.$P{D1}/$P{D2} with DefaultValue as new java.util.Date(); $P{S1}/$P{S2} with DefaultValue as new SimpleDateFormat("dd/MM/yyyy")).format($P{DATE_FROM})


Then, I create a simple Query in JasperServer, like

Code:
<br />
SELECT col_1 FROM myTable WHERE myDate BETWEEN to_date($P!{S1}, 'DD/MM/YYYY') and to_date($P!{S2}, 'DD/MM/YYYY')<br />
</td></tr></tbody></table><br />
That's what I done at the beginning. But it does not work because I always get empty report. If I hard-code in that Query(e.g. specify the date), I can get the correct report. So I am pretty sure that my SQL is fine.<br />
<br />
I run from JasperServer. I use JBoss and Oracle.<br />
Many thanks for your time.:blush:<br>Post edited by: ali88, at: 2007/08/03 07:05
ali88's picture
11
Joined: Jul 27 2007 - 6:02pm
Last seen: 11 years 10 months ago
0
If you're using your parameters inline - $P!{S1} - you'll need to quote them, just as you would if you were hardcoding a date in to_date.


Your SQL should be
Code:
SELECT col_1 FROM myTable WHERE myDate BETWEEN to_date('$P!{S1}', 'DD/MM/YYYY') and to_date('$P!{S2}', 'DD/MM/YYYY')</td></tr></tbody></table><br />
<br />
<br />
Notice there are quotes (') around the parameters.<br />
<br />
<br />
If you don't, Oracle will treat it as a numeric value so your date (for the 1 May 2007) would be calculated by oracle as 1 divided by 5 divided by 2007 (something like 0.000099) which would be an invalid date value.<br />
<br />
<br />
That will probably be why you're getting the invalid numeric value error from Oracle.<br />
<br />
<br />
Cheers<br />
<br />
<br />
C
chrish's picture
9
Joined: Jun 20 2007 - 2:24am
Last seen: 12 years 1 day ago
0
Hi, chrish, thanks. I've tried the quote thing but still got invalid numeric value error from Oracle. :S
So, if I don't quote them, I will always get empty report. If I quote them, I will get exception from Oracle: non-numberic....

Any suggestion? :blush:
ali88's picture
11
Joined: Jul 27 2007 - 6:02pm
Last seen: 11 years 10 months ago
0
Hi, all.
I think I found some clues.
If I quote them, the SQL query string which I can see in the log file will be

SELECT col_1 FROM myTable
WHERE myDate BETWEEN to_date('null', 'dd/MM/yyyy') AND TO_DATE('null','dd/MM/yyyy')

If I don't quote them, it looks like:

SELECT col_1 FROM myTable
WHERE myDate BETWEEN to_date(null, 'dd/MM/yyyy') AND TO_DATE(null,'dd/MM/yyyy')

That's why I always get empty report if I do not quote them; and always get exception if I quote them.:woohoo:

I put default value in iReport, in Default value expression. Did I forget anything? :blush:
ali88's picture
11
Joined: Jul 27 2007 - 6:02pm
Last seen: 11 years 10 months ago
0
A null value would explain it.


Looks like there aren't any values being passed through to the query parameter.


Try setting the default value in the String $P{S1} parameter using a conditional, similar to what was posted before:
Code:
($P{D1}==null?(new SimpleDateFormat("dd/MM/yyyy"«»)).format(new Date()):«»(new SimpleDateFormat("dd/MM/yyyy"«»)).format($P{D1}))</td></tr></tbody></table><br />
<br />
This allows for null parameters being passed into the report in the first place.<br />
<br />
<br />
You should repeat the process for your D2 parameter as well.<br />
<br />
<br />
Regards<br />
<br />
<br />
C
chrish's picture
9
Joined: Jun 20 2007 - 2:24am
Last seen: 12 years 1 day ago
0
I'm guessing that you have changed the names of your input controls from DATE_FROM and DATE_TO to D1 and D2 or they won't get passed top the report parameters of the same name.


C
chrish's picture
9
Joined: Jun 20 2007 - 2:24am
Last seen: 12 years 1 day ago
0
Hello, chrish. Thank you anyway.

I just made a try for default value using condition, but still got the execption ~ ORA-01858: a non-numeric character was found where a numeric was expected

About the name of parameter, I'm sure that I use the correct one. Typing D1, D2 in here just want to make my description more simple/clean.:P

I made a other try:change datatype of my input control and parameter in the report. I changed them to String and it works fine.......:whistle:

So now I'm wondering that why it doesn't work by using Date.:dry:
ali88's picture
11
Joined: Jul 27 2007 - 6:02pm
Last seen: 11 years 10 months ago
0
Hi, all. Good news!! :woohoo:
My report works fine now. :woohoo:
It's just because one of stupid......Anyway, many many thanks for everyone's help.
ali88's picture
11
Joined: Jul 27 2007 - 6:02pm
Last seen: 11 years 10 months ago
0
I'm not sure what the local protocols are regarding threadjacking, but ali88 seems satisfied, my problem easily qualifies as a "problem with parameter type date", and I hate creating new threads when old ones will do.

(Or might do - mods, feel free to split me off into a new thread, if that's more appropriate.)

I've got a report that needs to retrieve records from a Postgres database for "today & yesterday". That is, I want a query like this:

Code:
SELECT foo, bar, nargle<br />
  FROM mytable<br />
  WHERE mydate BETWEEN '$P{yesterday}' AND '$P{today}'</td></tr></tbody></table><br />
Both params are of class type java.lang.String. "Today" should default to the current date, or <br />
<br />
<table align="center" border="0" cellpadding="3" cellspacing="1" width="90%"><tbody><tr><td><b>Code:</b></td></tr><tr><td><pre>(new SimpleDateFormat("yyyy/MM/dd"«»)).format(new Date())</td></tr></tbody></table><br />
Sadly, this code in the iReport parameter returns this error:<br />
<br />
<table cellpadding="0" cellspacing="0" align="center" width="525" bgcolor="#cccccc"><tr><td>Error: Sourced file: inline evaluation of: ``(new SimpleDateFormat("yyyy/MM/dd")).format(new Date());'' : Can't new: void</td></tr></table><br />
And being not-too-good with Java, I have <b>no</b> idea what that means.  As near as I can tell, I'm essentially mirroring code samples of how to create & format dates.<br />
<br />
I eventually want to compute $P{yesterday} as "$P{today} - 1" (or whatever the equivalent Java code would be), but I seem to be stuck on the more basic creation of today's date.<br />
<br />
Any help at all would be much appreciated.  Please let me know if pasting in portions of the jrxml text would be useful.<br />
<br />
Post edited by: skaughtbowden, at: 2007/08/03 15:27<br>Post edited by: skaughtbowden, at: 2007/08/03 15:28
skaughtbowden's picture
Joined: Nov 14 2006 - 5:47am
Last seen: 12 years 7 months ago
0
I think it's probably best to split this question off as a new thread.

Having said that... I copy/pasted your code as a default value for a param named "today" and it worked great. This SQL snippet worked fine:
WHERE mydate < $P{today}

So your idea is fine. I can't see why you got that error.

Having said that... if you really want the query to always use today and yesterday, then you don't have any need for parameters at all. You'd be better off with SQL like this:

WHERE mydate between (CURRENT_DATE - 1) and CURRENT_DATE

-Matt
mdahlman's picture
8660
Joined: Mar 13 2007 - 2:43am
Last seen: 4 years 7 months ago
0
mdahlman wrote:
if you really want the query to always use today and yesterday, then you don't have any need for parameters at all. You'd be better off with SQL like this:

WHERE mydate between (CURRENT_DATE - 1) and CURRENT_DATE


And wouldn't you know it, after reading enough Java documentation to get a caffeine buzz, that's exactly the direction I took. I just wish the simple SQL solution had come to me earlier; took all of 5 minutes to implement.

Thanks very much for your reply, Matt.
skaughtbowden's picture
Joined: Nov 14 2006 - 5:47am
Last seen: 12 years 7 months ago
0
Dear all,

I experience exactly the same issue as Ali does:

Code:
<br />
java.sql.SQLException: ORA-01858: a non-numeric character was found where a numeric was expected<br />
</td></tr></tbody></table><br />
<br />
I am using JasperServer (WAR distribution for JBoss),<br />
develope the reports in JasperAssistant with data source pointing to Oracle.<br />
<br />
I tried following the suggestions mentioned in the post before but without success. I either run into the issue mentioned above or I receive an empty report as Ali does :(<br />
<br />
My parameters look like the following:<br />
<br />
<table align="center" border="0" cellpadding="3" cellspacing="1" width="90%"><tbody><tr><td><b>Code:</b></td></tr><tr><td><pre><br />
	<parameter name="Invoice_Number_From" class="java.lang.String"/><br />
	<parameter name="Invoice_Number_To" class="java.lang.String"/><br />
</td></tr></tbody></table><br />
<br />
and<br />
<br />
<table align="center" border="0" cellpadding="3" cellspacing="1" width="90%"><tbody><tr><td><b>Code:</b></td></tr><tr><td><pre><br />
	<parameter name="S_Invoice_Date_From" class="java.lang.String" isForPrompting="false"><br />
		<defaultValueExpression><![CDATA[($P{Invoice_Date_From}==null?(new SimpleDateFormat("dd/MM/yyyy"«»)).format(new Date()):«»(new SimpleDateFormat("dd/MM/yyyy"«»)).format($P{Invoice_Date_From}))]]></defaultValueExpression><br />
	</parameter><br />
	<parameter name="S_Invoice_Date_To" class="java.lang.String" isForPrompting="false"><br />
		<defaultValueExpression><![CDATA[($P{Invoice_Date_To}==null?(new SimpleDateFormat("dd/MM/yyyy"«»)).format(new Date()):«»(new SimpleDateFormat("dd/MM/yyyy"«»)).format($P{Invoice_Date_To}))]]></defaultValueExpression><br />
	</parameter><br />
</td></tr></tbody></table><br />
<br />
I tried to use following SQLs:<br />
<br />
<table align="center" border="0" cellpadding="3" cellspacing="1" width="90%"><tbody><tr><td><b>Code:</b></td></tr><tr><td><pre><br />
          BETWEEN  <br />
           to_date('$P!{S_Invoice_Date_From}', 'DD/MM/YYYY')<br />
          AND  <br />
           to_date('$P!{S_Invoice_Date_To}', 'DD/MM/YYYY')<br />
</td></tr></tbody></table><br />
<br />
... resulting in empty report and<br />
<br />
<table align="center" border="0" cellpadding="3" cellspacing="1" width="90%"><tbody><tr><td><b>Code:</b></td></tr><tr><td><pre><br />
          BETWEEN  <br />
           to_date($P{S_Invoice_Date_From}, 'DD/MM/YYYY')<br />
          AND  <br />
           to_date($P{S_Invoice_Date_To}, 'DD/MM/YYYY')<br />
</td></tr></tbody></table><br />
<br />
...gives me the non-numeric character error...<br />
<br />
How can I use the two parameters $P{Invoice_Number_From} and $P{Invoice_Number_To}<br />
with date format dd/MM/yyyy ?<br />
<br />
This is what is set in my calendar.properties:<br />
<table align="center" border="0" cellpadding="3" cellspacing="1" width="90%"><tbody><tr><td><b>Code:</b></td></tr><tr><td><pre><br />
date.format=dd/MM/yyyy<br />
datetime.format=dd/MM/yyyy HH:mm<br />
calendar.date.format=%m-%d-%Y<br />
calendar.datetime.format=%d/%m/%Y %H:%M<br />
</td></tr></tbody></table><br />
<br />
Any help is appriciated!<br />
Thanks in advance,....<br />
Fab
FMumm's picture
6
Joined: Feb 4 2007 - 6:31pm
Last seen: 12 years 4 months ago
0
Dear all,

I experience exactly the same issue as Ali does:

Code:
<br />
java.sql.SQLException: ORA-01858: a non-numeric character was found where a numeric was expected<br />
</td></tr></tbody></table><br />
<br />
I am using JasperServer (WAR distribution for JBoss),<br />
develope the reports in JasperAssistant with data source pointing to Oracle.<br />
<br />
I tried following the suggestions mentioned in the post before but without success. I either run into the issue mentioned above or I receive an empty report as Ali does :(<br />
<br />
My parameters look like the following:<br />
<br />
<table align="center" border="0" cellpadding="3" cellspacing="1" width="90%"><tbody><tr><td><b>Code:</b></td></tr><tr><td><pre><br />
	<parameter name="Invoice_Number_From" class="java.lang.String"/><br />
	<parameter name="Invoice_Number_To" class="java.lang.String"/><br />
</td></tr></tbody></table><br />
<br />
and<br />
<br />
<table align="center" border="0" cellpadding="3" cellspacing="1" width="90%"><tbody><tr><td><b>Code:</b></td></tr><tr><td><pre><br />
	<parameter name="S_Invoice_Date_From" class="java.lang.String" isForPrompting="false"><br />
		<defaultValueExpression><![CDATA[($P{Invoice_Date_From}==null?(new SimpleDateFormat("dd/MM/yyyy"«»)).format(new Date()):«»(new SimpleDateFormat("dd/MM/yyyy"«»)).format($P{Invoice_Date_From}))]]></defaultValueExpression><br />
	</parameter><br />
	<parameter name="S_Invoice_Date_To" class="java.lang.String" isForPrompting="false"><br />
		<defaultValueExpression><![CDATA[($P{Invoice_Date_To}==null?(new SimpleDateFormat("dd/MM/yyyy"«»)).format(new Date()):«»(new SimpleDateFormat("dd/MM/yyyy"«»)).format($P{Invoice_Date_To}))]]></defaultValueExpression><br />
	</parameter><br />
</td></tr></tbody></table><br />
<br />
I tried to use following SQLs:<br />
<br />
<table align="center" border="0" cellpadding="3" cellspacing="1" width="90%"><tbody><tr><td><b>Code:</b></td></tr><tr><td><pre><br />
          BETWEEN  <br />
           to_date('$P!{S_Invoice_Date_From}', 'DD/MM/YYYY')<br />
          AND  <br />
           to_date('$P!{S_Invoice_Date_To}', 'DD/MM/YYYY')<br />
</td></tr></tbody></table><br />
<br />
... resulting in empty report and<br />
<br />
<table align="center" border="0" cellpadding="3" cellspacing="1" width="90%"><tbody><tr><td><b>Code:</b></td></tr><tr><td><pre><br />
          BETWEEN  <br />
           to_date($P{S_Invoice_Date_From}, 'DD/MM/YYYY')<br />
          AND  <br />
           to_date($P{S_Invoice_Date_To}, 'DD/MM/YYYY')<br />
</td></tr></tbody></table><br />
<br />
...gives me the non-numeric character error...<br />
<br />
How can I use the two parameters $P{Invoice_Number_From} and $P{Invoice_Number_To}<br />
with date format dd/MM/yyyy ?<br />
<br />
This is what is set in my calendar.properties:<br />
<table align="center" border="0" cellpadding="3" cellspacing="1" width="90%"><tbody><tr><td><b>Code:</b></td></tr><tr><td><pre><br />
date.format=dd/MM/yyyy<br />
datetime.format=dd/MM/yyyy HH:mm<br />
calendar.date.format=%m-%d-%Y<br />
calendar.datetime.format=%d/%m/%Y %H:%M<br />
</td></tr></tbody></table><br />
<br />
Any help is appriciated!<br />
Thanks in advance,....<br />
Fab
FMumm's picture
6
Joined: Feb 4 2007 - 6:31pm
Last seen: 12 years 4 months ago
0
Hi Fab,

your input parameters Invoice_Number_From and Invoice_Number_To will need to be of type java.util.Date for my original solution to work.

Also if you have both the default values as new Date(), you will get an empty report as the start and end dates will be the same.

You could always use
Code:
to_date('$P!{S_Invoice_Date_To}', 'DD/MM/YYYY')+1</td></tr></tbody></table> in the SQL to include the 'to' day as well.<br />
<br />
Cheers<br />
<br />
Chris
chrish's picture
9
Joined: Jun 20 2007 - 2:24am
Last seen: 12 years 1 day ago
0
Dear Chrish,

thanks for your reply... sry, I guess, I have posted
the wrong paramters from my parameter list... this is of course the one I am talking about: ;-)

Code:
	<parameter name="Invoice_Date_From" class="java.util.Date"/><br />
	<parameter name="Invoice_Date_To" class="java.util.Date"/></td></tr></tbody></table><br />
<br />
Thanks for the hint (+1)....<br />
<br />
Anyway, I could also solve my problem using the query inside the jasperreport itself using this format:<br />
<br />
<table align="center" border="0" cellpadding="3" cellspacing="1" width="90%"><tbody><tr><td><b>Code:</b></td></tr><tr><td><pre>...BETWEEN  <br />
           to_date($P{S_Invoice_Date_From}, 'dd/MM/yyyy')<br />
          AND  <br />
           to_date($P{S_Invoice_Date_To}, 'dd/MM/yyyy')...</td></tr></tbody></table><br />
<br />
I always tried using the query from inside JasperServer to overwrite the query which was defined in my jasperreport which I was editing with JasperAssistant. As I used different queries for development (to get real data back without entering parameters) I never tried putting my final query in the jasperreport, instead only uploaded to the JasperServer and used it as resource.<br />
<br />
My issue got solved using query inside jasperreport instead from repository of JasperServer...<br />
<br />
Thanks,<br />
Fab
FMumm's picture
6
Joined: Feb 4 2007 - 6:31pm
Last seen: 12 years 4 months ago
0

Hola, pasé un buen tiempo con el problema de los parámetros de tipo fecha pero al final logre algo que me funcionó y espero que a alguien le sirva.

1)En mi reporte creo los parámetros de tipo java.util.Date o java.sql.Date

2) En mi codigo java cuando envío los parametros al servidor web de JasperServer no envío el tipo fecha sino lo convierto a tipo long 

Map<String, Object> params = new HashMap<String, Object>();
 
Long fechaI = new Fecha("2016-12-01").asDate().getTime();
Long fechaF = new Fecha("2016-12-31").asDate().getTime();
 
params.put("empresaId", 105);
params.put("fechaInicio", fechaI);
params.put("fechaFin", fechaF);
y con eso me funcionó, espero le sirva a alguien saludos.
 
elyogui83's picture
Joined: Jan 26 2017 - 5:45am
Last seen: 2 years 3 months ago
Feedback