Extract Month & Year from Date column as Calculated Field

[aluferrari]

Hi,

We are looking to create a new calculated field by extracting Month and Year (in 2 separate fields) from a column containing Date values. We have tried many things but nothing seems to be working.

We are on Oracle database and we have tried all TO_DATE functions and TO_CHAR functions but in vain. Please help us with correct syntax of DoMEL to achieve what we want.

Thanks in advance.

[ajinkya_c]

Hi,

Here is sample query

select

To_char(Sysdate, 'MONTH') as Month,

To_char(Sysdate, 'MM') as MM,

To_char(Sysdate, 'YYYY') as Year,

To_char(Sysdate, 'YY') as YY,

To_char(Sysdate, 'Month YYYY') as mmyy

from dual

hope this helps.

Thanks

Ajinkya

 

 

 

[aluferrari]

Thanks for the answer. We are aware of these functions in Oracle, but how do we add them in Jasper? Here is some background:

Our Employee table has hiredate column. We want to create a cross tab report that would show month-wise & year-wise employees hired.

 

Thanks again.

 

[ajinkya_c]

Hi,

Your query may be:

select

To_char( hiredate, 'MONTH') as Month,

To_char( hiredate, 'YYYY') as Year,

To_char hiredate, 'Month YYYY') as YearMonth 

from Employee

This may be your query, used month, year or  YearMonth in crosstab whichever suits your reporting requirement.

Thanks

Ajinkya

[aluferrari]

We dropped the idea of creating calculated field. Instead we created a derived table:

SELECT emp.*, TO_CHAR(HIREDATE,'MM') HIREMONTH, TO_CHAR(HIREDATE,'YY') HIREYEAR FROM EMPLOYEE emp;

Thank you Ajinkya.