Decimal to timestamp conversion: SAP data services

[#1]

Hello experts,

I have a problem with Unix timestamp conversion.

SAP Abap developers convert a date field to a unix value with code blocks. I want to read this value from the database and convert it back to date format. I am sharing this code block with you.

vbrk-fkdat = '2022.12.27'
CONSTANTS comp_nine(20) TYPE c VALUE '09182736455463728190'.
        datc = gw_vbrk-fkdat.
        TRANSLATE datc USING comp_nine.

As a result of this operation, they find the value datc = 79778772 and write it to a table. I want to read this datc value and convert it to date. How should I go about this. Have you met before? How can I do this process on Data Services?

[Oenedoef]

basically, you can convert the value back and then assign it to the date value?

Something like this:

CONSTANTS comp_nine_back(20) TYPE c VALUE '90817263544536271809'.
        TRANSLATE datc USING comp_nine_back.

    gw_vbrk-fkdat = datc.

[jmuiruri]

Greetings cader_berkay,

One way you could achieve this is by creating a custom function that does the following:

1. Take's in the ABAP masked date format e.g datc = 79778772.
2. Then extracts each masked character individually.
3. Unmasks the character based on the mask value you used e.g '09182736455463728190' e.g: returns: 0 = 9, 1 = 8, 2 = 7, 3 = 6, 4 = 5, 5 = 4, 6 = 3, 7 = 2, 8 = 1, 9 = 0
4. Then append the unmasked value to a string variable
5. Then convert the variable to type date
6. Then convert the date to the format you wish to

Custom Script Example

# This function take in the masked ABAP date column e.g 
#       $datc = '79778772'
# Then based on the mask characters:
#       $mask = '09187236455463728190'
# Converts the $datc to SAP Data Services type e.g 
#       $vbrk_fkdat = '2022.12.27';

#$Parameters Variables you will need
# 1. Return ==> Output
# 2. $abap_date ==> input

# Local variables
# 1. $unmasked_character_at_index ===> Stores the unmasked character at index
# 2. $masked_character_value_at_index  ===> Stroes masked character value at current index 
# 3. $index ==> tracks the index on the current masked character
# 4. $unmasked_characters_all  ===> Stores all the unmasked character 

# Check if the parsed value is not null
if ( $abap_date is not null )

# variable $index to keep track of the index while we iterate through the ABAP date characters
$index = 1;

# variable unmasked_characters_all will store the unmasked value/output in string format
$unmasked_characters_all = '';

# While loop to iterate through the abap date column until $index = len(abap date column)
 while ($index <=  length($abap_date) )
	begin
	   # Get the masked character value at index $index
	   $masked_character_value_at_index = cast(substr( $abap_date ,$index,1), 'int');

	# Unmasked character value at index $unmasked_character_value_at_index
        $unmasked_character_at_index  = decode( 
        ($masked_character_value_at_index = 0), 9,
        ($masked_character_value_at_index = 1), 8,
        ($masked_character_value_at_index = 2), 7,
        ($masked_character_value_at_index = 3), 6,
        ($masked_character_value_at_index = 4), 5,
        ($masked_character_value_at_index = 5), 4,
        ($masked_character_value_at_index = 6), 3,
        ($masked_character_value_at_index = 7), 2,
        ($masked_character_value_at_index = 8), 1,
        ($masked_character_value_at_index = 9), 0,
        -1);

        # Increment the index value
	$index = $index +1;
	# Append the unmasked character value to $unmasked_characters_all
	$unmasked_characters_all = $unmasked_characters_all || $unmasked_character_at_index ;
	end
#print('Final Result: '|| to_char(to_date( $unmasked_characters_all, 'yyyymmdd'), 'yyyy-mm-dd')  );
# Cast the unmasked results from string to date type, then convert the date format to 'yyyy-mm-dd', then return the results
Return to_char(to_date( $unmasked_characters_all, 'yyyymmdd'), 'yyyy-mm-dd');

Calling the script from the Job

You would then call the function from the query transform mapping by calling the function as shown below

abap_date_transform (ABAP_DATE_COLUMN_NAME)

References

• TRANSLATE
• decode
• Creating custom functions from the Tools menu

I am not an ABAP expert but, you should do some more research as i would like to think there is a better way to do this from the ABAP side.

Best Regards,

Joseph

SAP Support