topic Re: SAP read/convert time stamp column to date and time in Connectivity & Data Prep

SAP read/convert time stamp column to date and time

sush — Tue, 28 Dec 2021 20:23:33 GMT

Hi All,

I am trying to read data from SAP Tables and found the TIMESTMP column is having the value as - 7,336,837,220,000.

Could you please let me know how can u convert this value to MM/DD/YYYY HH:mm:ss:SSS?

Thanks

Re: SAP read/convert time stamp column to date and time

Hakan_Ronningberg — Thu, 12 Mar 2020 07:44:25 GMT

Hi,

A Timestamp in SAP is normally stored as data type DEC with the length of 15 (zero decimals). UTC Time Stamp in Short Form (YYYYMMDDhhmmss). For example the value 20.130.313.113.112 corresponds to 2013-03-13 11:31:12.

Your value looks strange. Is it really a Timestamp? Check the data type for your table/field in transaction SE11.

Regards,
Håkan

Re: SAP read/convert time stamp column to date and time

martinpohl — Thu, 12 Mar 2020 07:58:41 GMT

can you tell what table your want to load to?

Most tables contains a date field and a time field, but rarely commen in one field.

Also I know fields like in TCURR the date value is invers so value

79799686 is

99999999 - 79799686 = 20200311

Regards

Re: SAP read/convert time stamp column to date and time

sush — Thu, 30 Apr 2020 23:57:10 GMT

Sorry for the delayed response, I was able to convert the time stamp and load the data.

Below is what I have used

Timestamp(
Num(
DayStart(MakeDate(1990, 1, 1))
+
(
[TIMESTMP]/(10000 * 60 * 60 * 24)
)
, 'DD.MM.YYYY hh:mm:ss:SSS'
)) as DateTimeStamp,