convert varchar into integer

[manushreegupta]

hi

can anyone tell me how to convert jan, feb,........., dec into 1,2,........,12  ??
means converting varchar to integer in ireport.

i want to display all the months in numeric form by writing month as text in parameter..

pls help me and provide me with proper syntax to convert varchar into integer in ireport.

Please reply as soon as possible, as it is urgently required.

thanks

[robsil]

Firstly this is a JasperReports issue, not an iReport issue.. so you should really be posting in that forum.  Having said that...

So you have a string that represents a month, and want the numeric equivalent?  Just use a big expression that uses the <test>?<trueval>:<falseval> Java construct and chain this so that the "false" starts a new one for the subsequent month.

This is pseudo code since I am not at my Jasper workstation so you'll ahve to tweak... and remove comments.. just there so you can understand what I am writing.  For every month copy from //start copy area until //end copy area and replace the //COPY INTO HERE.

$F{MyDateAsVarChar} == null?null:    //always have a base boundry like this.

(

"Jan".equalsIgnoreCase($F{MyDateAsVarChar})?new Integer(1): (

//start copy area

"Feb".equalsIgnoreCase($F{MyDateAsVarChar})?new Integer(2): (

//COPY INTO HERE

)

//-end copy area

)

[Gaby38]

 You can also do the conversion in sql and get directly the number of the month in the query. I already did that in Oracle : i presume there are equivalent functions in other rdbms.

[robsil]

Gaby38
Wrote:
 

You can also do the conversion in sql and get directly the number of the month in the query. I already did that in Oracle : i presume there are equivalent functions in other rdbms.

Good idea... never think to hit it from a SQL point of view...

http://stackoverflow.com/questions/185520/convert-month-number-to-month-name-function-in-sql

 is the opposite direction.. but the reverse should hold up also.

Post Edited by robsil at 08/10/2010 20:35

[manushreegupta]

hi,

thanks for the quick reply..

my query in ireport is:

SELECT DISTINCT calendar_month_name, DATEPART(mm,CAST(calendar_month_name+'1, 1900' AS DATETIME))as Month_number
from Dim_Calendar
WHERE $P{calendar_month_name} = calendar_month_name
order by calendar_month_name

this gives the error as:

Error: SQL problem:conversion failed when converting the varchar value 'JANUARY'  to data type bit

when i remove the where clause, the query runs successfully giving the proper field name..

i want the parameter name(calendar_month_name) to be entered manually.. so i have to give it in where clause..

kindly tell me why this error is coming??

 

And one more problem is there:

i am having  a line chart in ireport which have list of months displaying in Y-axis as JAN, FEB, MAR,....... DEC

so now i want to display the months as 1, 2, 3,.......12 in Y-axis in ireport..
 

please give the proper steps to display months in numeric form in X- axis in ireport.

thanks

 

Post Edited by mgupta at 08/11/2010 07:35

[robsil]

I'd work on tweaking the SQL... if your doing that then it simply becomes an issue of having the right SQL vs. a JasperReports issue. I'd google based on what version of SQL your using (SQL SERVER, MySQL, etc.) and sort that out in a DB management tool first... then insert it into Jasper.