problem with date format

hy carl
i'm sorry for writting my message in french .
actually , that's the answer i'

On the contrary, I apologize for only being able to reply in English.  I can read a little French, Italian, and Spanish, but can not write any of them without a lot of research! :)  Yes, one of the main reasons to use a parameter is so you can then compare that value to values in your database, often in the WHERE clause, assuming you are using SQL.

For example, if you define a parameter of type java.util.Date called CutoffDate (DateButoir in French?), then the JasperSoft syntax for referring to a parameter in your SQL query or in one of the properties is $P{CutoffDate}, so you might have your SQL query look for birthdates that are on or earlier than this date:

SELECT
  COALESCE(PERSON.LAST_NAME, '') || COALESCE(',' || PERSON.FIRST_NAME, '') AS PERSON_NAME,
  PERSON.BIRTH_DATE
FROM
  CORE.PERSON AS PERSON
WHERE
  PERSON.BIRTH_DATE<=$P{CutoffDate}

Hope that helps out.  Define the parameter first before you use it in the query.  That way the report query dialog will show the available fields as in the attached screenshot instead of giving an error that $P{CutoffDate} is not defined.

Good luck!

Carl

I see now that you are the one who posted the question about specifying an operator in the parameter itself, probably like this:

SELECT
  LOAN.ID,
  LOAN.BALANCE
FROM
  CORE.LOAN AS LOAN
WHERE
  LOAN.BALANCE $P!{RelationalOperator} $P{InputAmount}

where I have defined RelationalOperator as a String with a default value of "=", but at runtime the operator could set the parameter to "<", ">=", etc..  I do not know for certain if that will work, although it does at least pass the syntax checker.  Personally, I do not like the idea of replacing part of the SQL query with a parameter value as is done with $P!{RelationalOperator} above, but it seems that several people are using this kind of technique.

Carl

Can you attach a sample of your jrxml file?  You are talking about the format of a date field in your output, not in a prompt, correct?  You can format a date field into a character within SQL, but then you can no longer treat it as a date directly--you can't do date arithmetic, easily check the month, day, or year of that field, sort by that field (although sorting would work if the character version still has yyyy/MM/dd order and pads MM and dd to two digits each).  I do like to do as much calculation as is practical within the SQL statement, but reformatting a date is a loss of information, so I prefer to do it as late in the process (right before displaying) as possible.  Another issue is that different versions of SQL support slightly different functions and syntax.  So Oracle and PostgreSQL both have TO_CHAR functions, for example, while some other versions do not.

Nevertheless, you asked for how to do it in SQL...here are a variety of date formats that work in DB2 version of SQL.  You will have to test to see which ones work in Oracle:

SELECT
  ACCOUNT.OPEN_DATE AS OPEN_DATE,
  SUBSTR(CHAR(ACCOUNT.OPEN_DATE),9,2) || '/' ||
    SUBSTR(CHAR(ACCOUNT.OPEN_DATE),6,2) || '/' ||
    SUBSTR(CHAR(ACCOUNT.OPEN_DATE),1,4) AS SUBSTR_DATE_CHAR,
  CAST(DAY(ACCOUNT.OPEN_DATE) AS VARCHAR(2)) || '/' ||
    CAST(MONTH(ACCOUNT.OPEN_DATE) AS VARCHAR(2)) || '/' ||
    CAST(YEAR(ACCOUNT.OPEN_DATE) AS VARCHAR(4)) AS CAST_DATE_CHAR,
  CHAR(ACCOUNT.OPEN_DATE,ISO) AS ISO_DATE_CHAR,
  CHAR(ACCOUNT.OPEN_DATE,USA) AS USA_DATE_CHAR,
  CHAR(ACCOUNT.OPEN_DATE,EUR) AS EUR_DATE_CHAR,
  CHAR(ACCOUNT.OPEN_DATE,JIS) AS JIS_DATE_CHAR
FROM
  CORE.ACCOUNT AS ACCOUNT

Produces the following output on my server:

 OPEN_DATE  SUBSTR_DATE_CHAR CAST_DATE_CHAR ISO_DATE_CHAR USA_DATE_CHAR EUR_DATE_CHAR JIS_DATE_CHAR
 2010-03-31 31/03/2010       31/3/2010      2010-03-31    03/31/2010    31.03.2010    2010-03-31
 2010-04-13 13/04/2010       13/4/2010      2010-04-13    04/13/2010    13.04.2010    2010-04-13
 2011-01-25 25/01/2011       25/1/2011      2011-01-25    01/25/2011    25.01.2011    2011-01-25
 2011-01-31 31/01/2011       31/1/2011      2011-01-31    01/31/2011    31.01.2011    2011-01-31
 2009-11-30 30/11/2009       30/11/2009     2009-11-30    11/30/2009    30.11.2009    2009-11-30
 2009-12-01 01/12/2009       1/12/2009      2009-12-01    12/01/2009    01.12.2009    2009-12-01

etc.

Carl

P.S.:  I could not resist throwing in one more technique that uses the DIGITS() function...I do not know which versions of SQL support it or how consistent they are in how many digits they produce (my version of DB2 SQL produces character output of ten digits), but it works better for me than CAST() because it pads the MM and dd to two digits each.  I still think that using a pattern within iReport is safer than any of these.