Convert Double data type to Date

[Anonymous]

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

[Anonymous]

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!

[Anonymous]

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