topic Convert Double data type to Date in Talend Studio

Convert Double data type to Date

Anonymous — Fri, 17 Mar 2017 13:41:18 GMT

Hi All,
I have a date written as double format in the source database (for example 42286,6144737963). In the destination database I need to convert it to Date format yyyy-MM-dd HH:mm:ss. Can I do it in tMap? I tried TalendDate.parseDate("yyyy-MM-dd HH:mm:ss", String.valueOf(row10.StartDATE )) but it returns an error java.lang.NumberFormatException: For input string: "null".
Any help would be much appreciated

Re: Convert Double data type to Date

Anonymous — Sat, 18 Mar 2017 14:52:16 GMT

First of all you have to get it clear how to interpret a Double value as Date. E.g. in Oracle and Excel the represents the integer part the days and the fraction the time. Of course the routine do you mentioned cannot do this because it expects a date formatted String, this is not what you have provided!
Here is an example of a routine which converts a Double into a Date:

public static Date getDuration(Double timeInExcel) {
    if (timeInExcel != null) {
        int wholeDays = (int) Math.floor(timeInExcel);
	int millisecondsInDay = (int) ((timeInExcel - wholeDays) * DAY_MILLISECONDS + 0.5);
	Calendar cal = Calendar.getInstance();
	cal.setTimeInMillis(0);
	cal.set(Calendar.DAY_OF_YEAR, wholeDays + 1);
	cal.set(Calendar.MILLISECOND, millisecondsInDay);
	return cal.getTime();
     } else {
	return null;
     }
}

This is a simple approach and I do not know in which way your Double value have to be interpreted as Date. This is the first question you have to ask!

Re: Convert Double data type to Date

Anonymous — Mon, 20 Mar 2017 15:32:32 GMT

Thank you for your answer jlolling.
I believe the double value is in Microsoft timestamp format. Example: http://www.silisoftware.com/tools/date.php?inputdate=42286&inputformat=microsoft