drousham Posted April 3, 2012 Share Posted April 3, 2012 Bonjour , je fais du reportage à partir d'une base de donnée oracle . je dispose d'un champs nommé année de type (YYYY/MM/DD ).sur ireport j'aimerais bien faire une requête sql de type select me permettant d'affciher cette table en format (DD/MM/YYYY) sans changer la database oracle <em></em> je vous remercie <em></em> Link to comment Share on other sites More sharing options...
cbarlow3 Posted April 3, 2012 Share Posted April 3, 2012 If the field in your database is a DATE datatype, then it is not really STORED in either YYYY/MM/DD or DD/MM/YYYY format. The issue is really just about how you control the DISPLAY of a date once you have retrieved it from the database. In iReport, you can drag your date field $F{anee} onto the detail band of the design view, and then for the Properties, set the "Pattern" to Date pattern "dd/MM/yyyy" as shown in the enclosed screenshot.Carl Link to comment Share on other sites More sharing options...
drousham Posted April 3, 2012 Author Share Posted April 3, 2012 hy carl i'm sorry for writting my message in french .actually , that's the answer i' Link to comment Share on other sites More sharing options...
drousham Posted April 3, 2012 Author Share Posted April 3, 2012 hy carl i'm sorry for writting my message in french .actually , that's the answer i'm greatfull carl carl tell me !! i create a parameter on a main report and i want to know if it's possible to use the arithmetic operators to define a condition :(like >,<,+<,=) Link to comment Share on other sites More sharing options...
cbarlow3 Posted April 3, 2012 Share Posted April 3, 2012 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_DATEFROM CORE.PERSON AS PERSONWHERE 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 Link to comment Share on other sites More sharing options...
drousham Posted April 3, 2012 Author Share Posted April 3, 2012 Merci beaucoup carl :) Link to comment Share on other sites More sharing options...
cbarlow3 Posted April 3, 2012 Share Posted April 3, 2012 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.BALANCEFROM CORE.LOAN AS LOANWHERE 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 Link to comment Share on other sites More sharing options...
drousham Posted April 4, 2012 Author Share Posted April 4, 2012 thank's carl carl about the date format it's not working . I do what you told me and it's not working (the format date is always in YYYY/MM/DD)do u not have another solution like a code in sql or something like that mizo Link to comment Share on other sites More sharing options...
cbarlow3 Posted April 4, 2012 Share Posted April 4, 2012 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_CHARFROM CORE.ACCOUNT AS ACCOUNTProduces 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-01etc.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.Post Edited by cbarlow3 at 04/04/2012 14:54 Link to comment Share on other sites More sharing options...
drousham Posted April 5, 2012 Author Share Posted April 5, 2012 hi,I founded a solution of my problem :) .in sql :select to_date(annne, 'yyyy/mm/dd') from my_table(thats inverse this 2003/04/21 to this 21/04/2003)that's all hh. thank's carl for your help Post Edited by drousham at 04/05/2012 10:06 Link to comment Share on other sites More sharing options...
drousham Posted April 5, 2012 Author Share Posted April 5, 2012 :) Post Edited by drousham at 04/05/2012 10:10 Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now