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

Highlighted
Four Stars

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

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

One Star

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

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

Regards,
TRF
Highlighted
Four Stars

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

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
Highlighted
One Star

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

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
Highlighted
Ten Stars

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

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.
Highlighted
Four Stars

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

Thanks TRF and rhall. It worked

2019 GARTNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

Best Practices for Using Context Variables with Talend – Part 2

Part 2 of a series on Context Variables

Blog

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog