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
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