TMap how to add Year to date where it is not found

[Anonymous]

Hello,

I have a table that should contain a Date like "21-03-2018 17:38" for example, the problem is some dates dont include the year (for example "21-03- 17:38") I would like to, in TMap, check if the date has a year or not, and add it where it is not present.

Note1: the Day is also sometimes "1-03-2018 17:38", meaning that it should be "01" in order to be the same length as the rest of the results

Note2: the date is transformed beforehand in the TMap as well since it starts off as "6 June 2018 at 17:51" and I turn it into "6-06-2018 17:51"

[Anonymous]

You say "Note2: the date is transformed beforehand in the TMap as well since it starts off as "6 June 2018 at 17:51" and I turn it into "6-06-2018 17:51""

THIS is where you want to add your year. If you are able to split up a String like "6 June 2018 at 17:51" and convert it to "6-06-2018 17:51", it seems pointless to process the date you have created again. Also, when you recreate your date the first time, ensure that the number format is consistent.

[Anonymous]

First of all, Thanks a lot for the quick reply!

The problem with what you suggested is that the data then comes as "6 June at 17:51" so theres no Year in the raw data but this only occurs in some of the data, not all of it, which is why I would need to add a comparison first like for example: When Date like "??-??-20?? ??:??" then end as Date, and if not then add "2019" for example.

I transform it by using a variable to change "June" to "-06-" and I Ereplace the 'at ' so the data comes out like the attached screen shot  (Capture.PNG) 

Capture.PNG

[akumar2301]

Normally 

string d MMMMM yyyy 'at' HH:mm will parse your input date pattern if it has year so with this pattern 

6 June 2019 at 17:51

16 June 2019 at 17:51

will be ok.

Now as yyyy is optional , you have two solutions

either you can do all in java code in tJava( tMap - instead of if...else use ternary operator) 

if (TalendDate.isDate(input_row.newColumn,"d MMMMM yyyy 'at' HH:mm")) {

System.out.println("Pattern 6 June 2018 at 17:51");
output_row.newColumn = TalendDate.parseDate("d MMMMM yyyy 'at' HH:mm",input_row.newColumn) ;

}else if (TalendDate.isDate(input_row.newColumn,"d MMMMM 'at' HH:mm")) {
System.out.println("Pattern 6 June at 17:51");
Integer yyyy = TalendDate.getPartOfDate("YEAR",new Date());

output_row.newColumn = TalendDate.parseDate("d MMMMM 'at' HH:mm yyyy",input_row.newColumn + " " + yyyy ) ;
}else{
System.out.println("Pattern did not match");
output_row.newColumn = null;
}

or

use custome liberary where Data parts can be optional ( Available with Java 😎

https://stackoverflow.com/questions/5897288/optional-parts-in-simpledateformat

[Anonymous]

How are you figuring out the year when it is not supplied? Why can't this method be done at stage one of the process?

[akumar2301]

@rhall 

It needs to be done at 1st step itself.

TalendDate.isDate(input_row.newColumn,"d MMMMM yyyy 'at' HH:mm")

will return false if there is no Year in input.If it is false ,

I am doing same validation against pattern "d MMMMM 'at' HH:mm" ( without yyyy)

By default,Java will parse it to year 1970 so I have added current year at the end of input String

and parsed it with pattern  "d MMMMM 'at' HH:mm yyyy"

[Anonymous]

I figure out the year by concatenating it from another column. Sadly, all transformations need to be done via Talend, I would've liked to do it from stage 1 myself.

[Anonymous]

Why can't you use that other column when you carry out the first transformation? Why is it not available then?

[Anonymous]

It is available, but the other column "Search_Time" is when the data was pulled, so its day and month may not be accurate, for example in 1 row I can have This as my main column '15 December at 20:18' while this is Search_Time is '2018-12-27T12:35:50.1671153+02:00'

Therefor making only the year accurate, so I want to concat the first 4 characters from Search_Time and put it after the word 'at' in my main column.

[Anonymous · Accepted solution]

Here is a routine that I have written that can be used in a tMap. It will do this for you. Please send Kudos and the solution to @uganesh since i have borrowed a lot of his code to do this quickly.....

	public static String convertDate(String mainColumn, String searchTime){
		String returnFormat = "";
		
		if (TalendDate.isDate(mainColumn,"d MMMMM yyyy 'at' HH:mm")) {

			
			returnFormat = TalendDate.formatDate("d MMMMM yyyy 'at' HH:mm", TalendDate.parseDate("d MMMMM yyyy 'at' HH:mm",mainColumn)) ;

		}else if (TalendDate.isDate(mainColumn,"d MMMMM 'at' HH:mm")) {
			
			String yyyy = searchTime.substring(0, 4);

			returnFormat = TalendDate.formatDate("d MMMMM yyyy 'at' HH:mm", TalendDate.parseDate("d MMMMM 'at' HH:mm yyyy",mainColumn + " " + yyyy )) ;
		}else{
			
			returnFormat = "The format is not understood - "+ mainColumn;
		}
		
		return returnFormat;
		
	}

If the format is something different from what you have stated, it will send the value back with a message saying that it could not be processed. You may want to change that.