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

[Anonymous]

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

[TRF]

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

Regards,
TRF

[Anonymous]

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

[TRF]

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

[Anonymous]

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.

[Anonymous]

Thanks TRF and rhall. It worked