Converting Simple Date format to JSON date format with User Defined Functions (UDF)

[SauryadeepDeb]

As an SAP Integration Developer working with SAP S/4 HANA API's with JSON Schema format, I encountered the necessity to adeptly convert simple date formats received from both SAP and non-SAP systems to JSON date format prior posting the payload to SAP S/4 HANA for required operations.

In this case, if payload posted to S/4 HANA consists of simple date format (DD/MM/YYYY), it reverts with date-related errors that arise due to unrecognized formats by the system.

To address this issue, I couldn't find an existing guide, prompting me to share my approach with fellow Integration Consultants.

Well recognizing the necessity for transformation, I opted to use Groovy script (UDF).

 

Scenario:

Our Integration layer receives data from various SAP as well as Non-SAP systems which requires to be posted to S/4 HANA system via API's after required conversions based on business requirements.
The date format present in the data was simple format (i.e. "DD/MM/YYYY") but S/4 HANA expects it in JSON date format (i.e. "/Date(478321400000)/").

 

Resolution:

To handle the conversion, I have developed a simple groovy UDF and added it to the message mapping layer of SAP CPI.

Steps to follow: 

1. Create your message mapping structure and select the relevant date fields which requires conversion.


2. Select the create Functions tab (highlighted below) for creation of the UDF Groovy script and give it a relevant name. (Note: You can first add the script in references section of Integration flow and assign script from here as well, by clicking on the adjacent icon.)
   After naming, click ok to proceed further.                                                                                                                                                                                                                                                                                                                                                                                                                             


3. Paste the following Groovy function.(Note: You don't need to do any modifications to the script apart from adjusting the date format based on the request payload input accordingly.)
   
   

   import com.sap.it.api.mapping.*
   
   
   
   def String JSONDateFormatting(String datefield) {
   
       
   
       if (datefield && !datefield.isEmpty()) {
   
           // adjust simple date format based on input
   
           def dateformat = new java.text.SimpleDateFormat("dd/MM/yyyy")
   
           def date = dateformat.parse(datefield)
   
           def timestamp = date.time
   
    
   
           def jsonDate = "/Date(${timestamp})/"
   
           return jsonDate
   
           
   
       } else {
   
           return null
   
       }
   
   }
   
   ​

   
    
   
    


4. Map the relevant field with the function pallet.                                                                                                                                                                                                                                   

Voila, you're all set!

 

Let's test now to check how things go ahead.

 

Development Assessments:

The request payload was triggered from an end system was passed through the message mapping structure for required transformation. 

(It's an XML structure, I have internationally hid rest of the data apart from the date field.)

                                   Prior to message mapping transformation.

 

The date field with simple date format was likewise transformed to JSON date format using the UDF.

After message mapping transformation.

 

 

Conclusion: 

You have the option to include the User-Defined Function (UDF) in the necessary date fields at message mapping structure for JSON format transformation.

Alternatively, achieving the same functionality is also possible by employing a Groovy script within the Integration Flow, utilizing this function and importing specific JAR files. The outcome can be stored in Header/Exchange property and retrieved when needed.
While both approaches are effective, using a Groovy script involves an additional step in the Integration Flow.

 

Thanks for reading!

[Bais]

Hi thank you for your explain but on S4H business hub there is a strange thing about schema:

on schema/json example they show you:

 "MfgOrderCreationDate": "/Date(1492041600000)/",



but they accept:

 

"MfgOrderCreationDate": "2023-12-12T00:00:00",​

without any conversion in epoch.

F.B.

[DG]

If it is just date the time zone may not be applicable but once you get to use timezones that could have an impact.

[SauryadeepDeb]

Hello,

Yes, Indeed. It worked fine with this approach, I wasn't aware of this behavior for the Schema.

Thank you 🙂

[SauryadeepDeb]

Hello,

I'd like to express my gratitude for all your contributions. Thank you!

Certainly, this groovy is designed specifically for a simple date format without time zones and using it with time zones will have an impact.

I hadn't explored this path initially, as it wasn't part of the requirements, but I'm definitely open to exploring it further now.

Thank you 🙂