topic Covert integer (DateKey) to Date (MM-dd-yyyy) in Talend Studio

Covert integer (DateKey) to Date (MM-dd-yyyy)

Anonymous — Tue, 23 Aug 2016 15:23:55 GMT

Hi,
How to convert integer to Date. For example I have 73049, ideally when convert this to date in Excel, it 12-31-2099. I tried new java.util.Date(<column_name>) but its giving as 12-31-1969.
Please guide me.
Thanks,
Bharath

Re: Covert integer (DateKey) to Date (MM-dd-yyyy)

TRF — Tue, 23 Aug 2016 16:47:11 GMT

column_name should represent the number of second from 1970-01-01 as a Long variable. This is not the case here.

Regards,
TRF

Re: Covert integer (DateKey) to Date (MM-dd-yyyy)

Anonymous — Wed, 24 Aug 2016 01:28:27 GMT

Hi

@TRF, can you elaborate more on this. I converted integer to long, but it didnt work. the exepcted output for Date key 73049 is 12-31-2099.

Thanks,
Bharath

Re: Covert integer (DateKey) to Date (MM-dd-yyyy)

TRF — Wed, 24 Aug 2016 22:10:37 GMT

In Excel, 73049 is the number of days between 1900-01-01 and 2099-12-31 (approx 200 years).
As Date(Long millisec) expect a number of milliseconds from 1970-01-01, the result give you 1969-12-31 because your timezone is GMT -8 (or something like that because you're in India). 1970-01-01 + 73 seconds - 8 hours : that's it, you're in 1969!
So, replace the value you pass to the Date() method by column_name*24L*3600L*1000L (column_name must be a Long datatype variable).
As expected, the result is 2170-01-01 (1970-01-01 + 200 years).
Regards,
TRF

Re: Covert integer (DateKey) to Date (MM-dd-yyyy)

Anonymous — Wed, 24 Aug 2016 22:47:45 GMT

What TRF said about Excel is correct. Another solution to resolve this is below...

Date myDate = routines.TalendDate.parseDate("yyyy-MM-dd", "1899-12-31");
myDate = routines.TalendDate.addDate(myDate, 73049, "dd");

System.out.println(myDate);

However, it will not take into account time.

Re: Covert integer (DateKey) to Date (MM-dd-yyyy)

Anonymous — Thu, 25 Aug 2016 15:35:08 GMT

Thanks TRF and rhall. It worked