How to do epoch date conversion to standard date and vice versa

[ambily_varghese]

I have faced a scenario of input date column coming from a JDE source and output will be loaded into a target in which date has to be stored as milliseconds.

After doing a search I got to know that epoch time is the format for storing date in millisecond.Epoch time, also known as Unix time, is the number of seconds that have elapsed since January 1, 1970

For converting JDE date to standard date format we can use JDE_Date() function.

But there is no inbuilt function in SAP DataServices to convert date into epoch time.

1.For converting JDE date to standard date use JDE_Date()



 

2.For converting standard date in to epoch date,use below

date_diff(to_date('1970.01.01','YYYY.MM.DD'),To_Date(Query.standard_date,'YYYY.MM.DD'),'DD')*86400000.0

output column will be decimal(38,0) datatype.

Here date_diff() will calculate the number of days difference from Jan 1,1970 to the input date given.

86400000 is the value derived by multiplying 24 hours * 60 minutes *60 seconds *1000

No: of days difference * 864000 will give the output in millisecond.Because of the integer size limitation I have used decimal datatype.

 

Lets see the scenario of epoch to standard date conversion as well.

My target is IBM Tririga in which date is saved as millisecond.Here doing the conversion of epoch format to standard date format

My input table in SAP DS has data from tririga.

Date column value is 2615932800000 ( epoch date format)

expected output is 2052.11.23 (yyyy.mm.dd) format

Solution is to_date('1970.01.01','YYYY.MM.DD') + Query.tririga_date/1000/60/60/24

 

Tririga date format is known as  unix time or epoch time or posix time.

You can check output here https://www.epochconverter.com/

It gives output as GMT: Saturday, November 23, 2052 12:00:00 AM

Hope this will be helpful for someone who uses epoch conversion