Adding dates in tMap

One Star

Adding dates in tMap

Hi,
I hope this is an easy question for someone.
I want to add two dates in a tMap. I thought that the following expression would work as both are date type fields:
row1.date + row2.date
Note that both rows are date/time data with the date pattern "dd-MM-yyyy HH:mm:ss" applied. For example
row1.date: 1900-01-00 00:15:00
row2.date: 2012-06-30 00:00:00
so the expected result would be: 2012-06-30 00:15:00

thanks,
David
One Star

Re: Adding dates in tMap

Hi David
The expression in tMap.
TalendDate.parseDate("yyyy-MM-dd H:mm:s",(TalendDate.getPartOfDate("YEAR",row2.date)+"-"+
(TalendDate.getPartOfDate("MONTH",row2.date)+1)+"-"+
TalendDate.getPartOfDate("DAY_OF_MONTH",row2.date)+" "
+TalendDate.getPartOfDate("HOUR",row1.date)+":"+TalendDate.getPartOfDate("MINUTE",row1.date)+":"+TalendDate.getPartOfDate("SECOND",row1.date)))

The date pattern of this output column should be yyyy-MM-dd HH:mm:ss.
Regards,
Pedro
One Star

Re: Adding dates in tMap

Pedro,
thank you for your answer. Your solution is working for me.
Thanks again,
David
One Star

Re: Adding dates in tMap

Hi Pedro,
Your solution was working but my result is not in 24 hour time, but in 12 hour time. On other words, I am expecting a result of "2012-03-17 23:30:00" but I get "2012-03-17 11:30:00"
I have used the expression that you provided exactly. All of the date patterns are "yyyy-MM-dd HH:mm:ss". I have also checked the both the row inputs (with tLogRow) to the expression and these are in the correct format. Any ideas why?
Thanks in advance,
David
One Star

Re: Adding dates in tMap

Hi David
First, you have to create a custom routine.
public class HOUR_OF_DAY {

public static int getPartOfDate(String partName, Date date) {
if (partName == null || date == null)
return 0;
int ret = 0;
String[] fieldsName = { "HOUR_OF_DAY"};
java.util.List<String> filedsList = java.util.Arrays.asList(fieldsName);
Calendar c = Calendar.getInstance();
c.setTime(date);
switch (filedsList.indexOf(partName)) {
case 0:
ret = c.get(Calendar.HOUR_OF_DAY);
break;
default:
break;
}
return ret;
}
}

Then use the following expression.
TalendDate.parseDate("yyyy-MM-dd H:m:s",(TalendDate.getPartOfDate("YEAR",row2.date)+"-"+
(TalendDate.getPartOfDate("MONTH",row2.date)+1)+"-"+
TalendDate.getPartOfDate("DAY_OF_MONTH",row2.date)+" "
+HOUR_OF_DAY.getPartOfDate("HOUR_OF_DAY",row1.date) +":"+TalendDate.getPartOfDate("MINUTE",row1.date)+":"+TalendDate.getPartOfDate("SECOND",row1.date)))

Regards,
Pedro
One Star

Re: Adding dates in tMap

Pedro,
thanks for your solution - it is working great!
Regards,
David
One Star

Re: Adding dates in tMap

Hi
Glad to help you.
Feel free to ask any question here.
Regards,
Pedro

What’s New for Talend Spring ’19

Watch the recorded webinar!

Watch Now

Tutorial

Introduction to Talend Open Studio for Data Integration.

Watch

Downloads and Trials

Test drive Talend's enterprise products.

Downloads

Definitive Guide to Data Integration

Practical steps to developing your data integration strategy.

Download