Problem with Parameter type Date

[rafa]

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

[kelly]

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.

[chrish]

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}, then use the simple date formatter to set the default value using the original passed-in date as a source.

 

The Default field for the QUERY_X_DATE parameter should look like this:

 

Code:

[code](new SimpleDateFormat("yyyy-MM-dd"«»)).format($P{X_DATE})

 

Then you can use

Code:

[code]SQL_COLUMN = $P{QUERY_X_DATE}

for comparison in the SQL.

 

Cheers

 

C

 

Post edited by: chrish, at: 2007/07/13 19:24

Post edited by: chrish, at: 2007/07/13 19:24

[kelly]

You are RIGHT. THANKS A LOT. :laugh:

[mvisman]

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

[chrish]

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

[ali88]

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:

[chrish]

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})

 

 

The X in my parameter names should be replaced with whatever you've called your parameters.

 

 

The most important thing is to make sure all your input controls and parameters are named and referenced correctly.

 

 

Other than that, you could try running the SQL directly against the DB, to see what's happening.

 

C

[ali88]

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:

date.format=dd/MM/yyyy
datetime.format=dd/MM/yyyy HH:mm

 

On my jasperserver, I choose en_US as Locale and CET as TimeZone.

 

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).

 

Could you give me a hint or suggestion that anything I missing? Thank you. :blush:

[lucianc]

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

[ali88]

Hi, Lucian. Thanks for your reply.

Yes, my setting is

 

Code:

date.format=dd/MM/yyyy
datetime.format=dd/MM/yyyy HH:mm
calendar.date.format=%d/%m/%Y
calendar.datetime.format=%d/%m/%Y %H:%M

 

I use JBoss 4.0.5

 

I'm wordering that is it possible the properties files are not applied after my deployment? :dry:

 

Thank you.

[lucianc]

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

[ali88]

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:

($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())))

 

2.(jasperserver)Create a Query -

Code:

[code] SELECT COL_1 FROM myTable WHERE myDate BETWEEN to_date($P{SDATE_FROM},'DD/MM/YYYY') AND to_date('31/12/2007','DD/MM/YYYY')

 

3.(jasperserver)Create a input control -

DATE_FROM with DataType Date

 

4.(jasperserver)Create a jasperreport -

this report use jrxml from 1., query from 2., and one input from 3.

 

Finally, I run my report and always get the issues.

 

Thanks for any help for me. :blush:

[lucianc]

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})

 

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.

 

Regards,

Lucian

[ali88]

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:

[lucianc]

$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?

[chrish]

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')

 

 

As Lucian said, your complicating the matter if you use

Code:

[code]($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())))

 

You're changing the numeric Date into a String and then changing it back to a Date and then back to a String.

 

 

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.

 

 

All you need is this in your default value for $P{SDATE_FROM}:

Code:

[code]($P{DATE_FROM}==null?"01/01/2005":«»(new SimpleDateFormat("dd/MM/yyyy"«»)).format($P{DATE_FROM}))

 

 

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?

 

 

Let me know if any of this helps.

 

 

Cheers

 

 

C

 

 

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:

Code:

[code]to_date('31/12/2007 23:59:59','DD/MM/YYYY HH24:MI:«»SS')

[chrish]

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')

 

 

As Lucian said, your complicating the matter if you use

Code:

[code]($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())))

 

You're changing the numeric Date into a String and then changing it back to a Date and then back to a String.

 

 

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.

 

 

All you need is this in your default value for $P{SDATE_FROM}:

Code:

[code]($P{DATE_FROM}==null?"01/01/2005":«»(new SimpleDateFormat("dd/MM/yyyy"«»)).format($P{DATE_FROM}))

 

 

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?

 

 

Let me know if any of this helps.

 

 

Cheers

 

 

C

 

 

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:

Code:

[code]to_date('31/12/2007 23:59:59','DD/MM/YYYY HH24:MI:«»SS')

[ali88]

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:

SELECT col_1 FROM myTable WHERE myDate BETWEEN to_date($P!{S1}, 'DD/MM/YYYY') and to_date($P!{S2}, 'DD/MM/YYYY')

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.

 

I run from JasperServer. I use JBoss and Oracle.

Many thanks for your time.:blush:

Post edited by: ali88, at: 2007/08/03 07:05

[chrish]

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')

 

 

Notice there are quotes (') around the parameters.

 

 

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.

 

 

That will probably be why you're getting the invalid numeric value error from Oracle.

 

 

Cheers

 

 

C

[ali88]

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]

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:

[chrish]

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}))

 

This allows for null parameters being passed into the report in the first place.

 

 

You should repeat the process for your D2 parameter as well.

 

 

Regards

 

 

C

[chrish]

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

[ali88]

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: