Excel: Convert Double type into Date type

[gt0731]

Hi I am tyring to read  excel. In that,  following observations

I have date column in excel, but when I'm reading in my talend application I'm getting value as number

Example

Excel Date

1/1/2013 03:44:55 AM

Am getting as  means talend metadata reading it as double type 

41275.00

How to convert  double type to date type

We tried this java rountine but not able to call in  this job . 

package routines;



import java.text.SimpleDateFormat;
import java.util.Date;
public class DoubletoDatetype {

	public static void main(String[] args) {
		double myDouble = -242528463.775282;
		long myLong = System.currentTimeMillis() + ((long) (myDouble * 1000));
		System.out.println(myLong);

		Date itemDate = new Date(myLong);
		String myDateStr = new SimpleDateFormat("dd-MM-yyyy").format(itemDate);
		System.out.println(myDateStr);
        }
        
    }

[gt0731]

We tried the same way as per above instruction 

Getting this error 


org.talend.designer.runprocess.ProcessorException: Job compile errors 
At least job "DebugTime" has a compile errors, please fix and export again.
Error Line: 961
Detail Message: routines cannot be resolved
There may be some other errors caused by JVM compatibility. Make sure your JVM setup is similar to the studio.

	at org.talend.designer.runprocess.JobErrorsChecker.checkLastGenerationHasCompilationError(JobErrorsChecker.java:338)

	at org.talend.designer.runprocess.DefaultRunProcessService.checkLastGenerationHasCompilationError(DefaultRunProcessService.java:464)

	at org.talend.designer.runprocess.RunProcessService.checkLastGenerationHasCompilationError(RunProcessService.java:316)

	at org.talend.designer.runprocess.ProcessorUtilities.generateBuildInfo(ProcessorUtilities.java:812)

	at org.talend.designer.runprocess.ProcessorUtilities.generateCode(ProcessorUtilities.java:586)

	at org.talend.designer.runprocess.ProcessorUtilities.generateCode(ProcessorUtilities.java:1736)

	at org.talend.designer.runprocess.RunProcessContext$1.run(RunProcessContext.java:582)

	at org.eclipse.jface.operation.ModalContext.runInCurrentThread(ModalContext.java:466)

	at org.eclipse.jface.operation.ModalContext.run(ModalContext.java:374)

	at org.eclipse.jface.dialogs.ProgressMonitorDialog.run(ProgressMonitorDialog.java:527)

	at org.eclipse.ui.internal.progress.ProgressMonitorJobsDialog.run(ProgressMonitorJobsDialog.java:284)

	at org.eclipse.ui.internal.progress.ProgressManager.run(ProgressManager.java:1190)

	at org.talend.designer.runprocess.RunProcessContext.exec(RunProcessContext.java:534)

	at org.talend.designer.runprocess.ui.ProcessComposite.exec(ProcessComposite.java:1401)

	at org.talend.designer.runprocess.ui.views.ProcessView$RunAction.run(ProcessView.java:701)

	at org.talend.designer.runprocess.ui.actions.RunProcessAction.run(RunProcessAction.java:58)

	at org.talend.designer.core.debug.JobLaunchConfigurationDelegate$1.run(JobLaunchConfigurationDelegate.java:84)

	at org.eclipse.swt.widgets.RunnableLock.run(RunnableLock.java:35)

	at org.eclipse.swt.widgets.Synchronizer.runAsyncMessages(Synchronizer.java:136)

	at org.eclipse.swt.widgets.Display.runAsyncMessages(Display.java:4147)

	at org.eclipse.swt.widgets.Display.readAndDispatch(Display.java:3764)

	at org.eclipse.e4.ui.internal.workbench.swt.PartRenderingEngine$9.run(PartRenderingEngine.java:1151)

	at org.eclipse.core.databinding.observable.Realm.runWithDefault(Realm.java:332)

	at org.eclipse.e4.ui.internal.workbench.swt.PartRenderingEngine.run(PartRenderingEngine.java:1032)

	at org.eclipse.e4.ui.internal.workbench.E4Workbench.createAndRunUI(E4Workbench.java:148)

	at org.eclipse.ui.internal.Workbench$5.run(Workbench.java:636)

	at org.eclipse.core.databinding.observable.Realm.runWithDefault(Realm.java:332)

	at org.eclipse.ui.internal.Workbench.createAndRunWorkbench(Workbench.java:579)

	at org.eclipse.ui.PlatformUI.createAndRunWorkbench(PlatformUI.java:150)

	at org.talend.rcp.intro.Application.start(Application.java:265)

	at org.eclipse.equinox.internal.app.EclipseAppHandle.run(EclipseAppHandle.java:196)

	at org.eclipse.core.runtime.internal.adaptor.EclipseAppLauncher.runApplication(EclipseAppLauncher.java:134)

	at org.eclipse.core.runtime.internal.adaptor.EclipseAppLauncher.start(EclipseAppLauncher.java:104)

	at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:380)

	at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:235)

	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

[gt0731]

Here is the code  for  routine. 

package routines;

import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.TimeZone;

public class ConvertDoubleToDate {
	public static  void main(String[] args) throws InterruptedException {
	        Date date = new Date();
	        DateFormat format = new SimpleDateFormat("dd/MM/yyyy HH:mm:ss");
	        format.setTimeZone(TimeZone.getTimeZone("Etc/UTC"));
	        String formatted = format.format(date);
	        System.out.println(formatted);
	        format.setTimeZone(TimeZone.getTimeZone("Australia/Sydney"));
	        formatted = format.format(date);
	        System.out.println(formatted);
	    }
	}

[gt0731]

Here is detail : 

Input date  : double type looks like 

|42888.6743287037 |
|42888.677453703705|
|42888.68003472222 |
|42891.37412037037 |
|42891.41011574074 |
|42891.41289351852 |
|42891.41289351852 |
|42891.41289351852 |
|42891.41289351852 |
|42891.41289351852 |
|42891.4190625

Output expected  : date type 
08-11-2018 

[Anonymous]

Hello have you tried your code in a java compiler?

if it works check your jdk version

https://community.talend.com/t5/Installing-and-Upgrading/Configure-to-use-a-JVM/td-p/112893

Edit : I think there is problem with your data, it's not even a timestamp and i don't see how you will convert them into a datetime

[gt0731]

I corrected the JVM issue but after ecxecuting routine . does not give me result. 

i need to convert excel date (double type ) into date type in outout. 

Please let me know if you have any  java routine or  expression for it 

[Anonymous]

I don't think a kind of routine deals with your request as like i said, you double is not a timestamp.

I suggest you once again to check at your data source and i would try to modify and understand how are generated those doubles who are not timestamp values.

Unless your double is a special timestamp specific to your country.

The problem is not just how to convert your double to a date, we solved that. But now you need to find the mathematic solution to convert your double into a correct timestamp and then convert it to a format date with your routine / tjavarow.

Good luck