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

[chafer]

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.

[Anonymous]

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

[chafer]

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.

[chafer]

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

[Anonymous · Accepted solution]

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);

[chafer]

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);