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

Five Stars

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

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"


Accepted Solutions
Community Manager

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

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 @akumar2301 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.


All Replies
Community Manager

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

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.

Five Stars

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

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) 

Eleven Stars

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

 

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

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

 

Regards
Abhishek KUMAR
Community Manager

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

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?

Eleven Stars

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

@rhall_2_0 

 

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"

 

 

Regards
Abhishek KUMAR
Highlighted
Five Stars

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

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.

Community Manager

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

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

Five Stars

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

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.

Community Manager

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

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 @akumar2301 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.

Five Stars

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

Thank you guys both!, a well deserved Kudos Smiley Happy

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

Downloads and Trials

Test drive Talend's enterprise products.

Downloads