[resolved] Transform a Date field to a Fiscal Year and Month-of-Year

Four Stars

[resolved] Transform a Date field to a Fiscal Year and Month-of-Year

Is there a way in Talend TOS to convert a date, such as 7/1/2010, to a Fiscal Year, such as 2011? I need to create a Fiscal Year (int) and Fiscal Month-of-Year (int) from a transaction date field.
Example (starting of Fiscal Date is July 1st, i.e., 7/1/2010):
Date Cal Year Cal Mth-of-yr
7/1/2009 2009 7
6/30/2010 2010 6
9/6/2011 2011 9

Date Fiscal Year FY Mth-of-yr
7/1/2009 2010 1
6/30/2010 2010 12
9/6/2011 2012 3

In MS Access it is done this way:
fy_year: Year()-IIf(<DateSerial(Year(),7,1),1,0)+1
Thank you for any help.

Accepted Solutions
Seventeen Stars

Re: [resolved] Transform a Date field to a Fiscal Year and Month-of-Year

To get any parts of a Date you have to use the class Calendar (it is an interface and there are a lot of implementations like GregorianCalendar).
To get the year do this:
Calendar c = Calendar.getInstance();
c.setTime(myDate);
int year = c.get(Calendar.YEAR);

All Replies
Community Manager

Re: [resolved] Transform a Date field to a Fiscal Year and Month-of-Year

Hi
The Fiscal year could be different in different country. As you described, if I understand you well, if the date larger than or equals to 07/01/xxxx, the Fiscal Year will be (xxxx+1), otherwise, the Fiscal Year is xxxx. In Talend, you can write an custom routine to complete this logic conversion and returns the Fiscal Year.
For example, go to Repository-->Code-->Routines and create a new routine called "MyRoutineDemo",
package routines;

public class MyRoutineDemo {

public static int getFiscalYear(java.util.Date date) {
int year=date.getYear()+1900;
int fiscalYear=0;
java.util.Date standarDate=TalendDate.parseDate("MM/dd/yyyy","07/01/"+year);
if(TalendDate.compareDate(date, standarDate)>=0){
fiscalYear=year+1;
}else{
fiscalYear=year;
}

return fiscalYear;
}
}

And then, call this routine on tMap to get the fiscal year, the detailed job design as seen below.
Result on console:
Starting job forum21775 at 12:04 03/02/2012.
connecting to socket on port 3860
connected
.----------+----------+-----------.
| tLogRow_1 |
|=---------+----------+----------=|
|date |fiscalYear|fiscalMonth|
|=---------+----------+----------=|
|07/01/2009|2010 |1 |
|06/30/2010|2010 |12 |
|09/06/2011|2012 |3 |
'----------+----------+-----------'
disconnected
Job forum21775 ended at 12:04 03/02/2012.

Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
Four Stars

Re: [resolved] Transform a Date field to a Fiscal Year and Month-of-Year

I really appreciate this fix. Just wanted you to know also there is a warning on part of this code: date.getYear() - The method getYear() from the type Date is deprecated. Thank you so much.
Four Stars

Re: [resolved] Transform a Date field to a Fiscal Year and Month-of-Year

The "date.getYear()" in line is deprecated. What is an equivalent code for it that is not deprecated?
int year=date.getYear()+1900;
Seventeen Stars

Re: [resolved] Transform a Date field to a Fiscal Year and Month-of-Year

To get any parts of a Date you have to use the class Calendar (it is an interface and there are a lot of implementations like GregorianCalendar).
To get the year do this:
Calendar c = Calendar.getInstance();
c.setTime(myDate);
int year = c.get(Calendar.YEAR);
Four Stars

Re: [resolved] Transform a Date field to a Fiscal Year and Month-of-Year

It looks like I need to have the Talend Routine code shong wrote in 2012's post re-done since the Calendar snipit part won't work with the rest of his Talend Routine's code?
To get any parts of a Date you have to use the class Calendar (it is an interface and there are a lot of implementations like GregorianCalendar).
To get the year do this:
Calendar c = Calendar.getInstance();
c.setTime(myDate);
int year = c.get(Calendar.YEAR);