Format Date Time in OIC

Introduction

Formatting date and time in Oracle Integration Cloud is one of the most common requirements you will encounter in real-time integrations. Whether you are integrating with Oracle Fusion applications, external REST APIs, or legacy systems, handling date-time formats correctly is critical for data accuracy and successful processing.

In real implementations, different systems use different date formats like:

• YYYY-MM-DD (ISO format)
• DD-MON-YYYY (Oracle style)
• MM/DD/YYYY (US format)
• Timestamps with timezone (2026-03-31T10:30:00Z)

As a consultant, you must know how to transform, format, and validate date-time values inside OIC Gen 3 integrations using XPath functions, mapping tools, and assign actions.

What is Date Time Formatting in OIC?

Date-time formatting in OIC refers to the process of:

• Converting one date format into another
• Extracting parts of a date (year, month, day)
• Adding or subtracting time
• Converting time zones
• Handling timestamps across systems

OIC primarily uses XPath 2.0 functions and built-in mapper functions to handle date-time transformations.

Key Features of Date-Time Handling in OIC Gen 3

1. Built-in XPath Functions

OIC provides standard functions like:

• current-date()
• current-dateTime()
• format-dateTime()
• adjust-dateTime-to-timezone()

2. Mapper Support

Drag-and-drop mapper allows:

• Easy date transformation
• Expression builder usage
• Reusable mapping logic

3. Time Zone Conversion

Supports conversion between:

• UTC
• Local system time
• Custom time zones

4. Integration with Fusion Applications

Handles:

• Fusion HCM dates (Effective Start Date, End Date)
• ERP transaction timestamps
• SCM shipment dates

Real-World Integration Use Cases

Use Case 1: Employee Hire Integration (HCM)

When loading employee data into Fusion HCM:

• Source system sends: 03/31/2026
• Fusion expects: 2026-03-31

👉 You must convert format before sending payload.

Use Case 2: Invoice Processing (ERP)

External system sends timestamp:

• 2026-03-31T10:45:30Z

ERP requires:

• 31-MAR-2026

👉 Requires format transformation + timezone handling.

Use Case 3: File-Based Integration (FBDI)

While generating CSV for FBDI:

• Date must match Oracle format: YYYY/MM/DD
• Incorrect format → File rejection

👉 Date formatting becomes critical for batch processing.

Architecture / Technical Flow

Typical OIC Gen 3 date-time processing flow:

1. Source Trigger
   • REST / SOAP / File / FTP Adapter
2. Mapper
   • Transform incoming date format
3. Assign Action
   • Apply date functions if needed
4. Target System
   • Send formatted date

Prerequisites

Before working with date-time in OIC:

• Basic understanding of XPath expressions
• Familiarity with OIC Mapper
• Knowledge of source and target date formats
• Awareness of timezone requirements

Step-by-Step Build Process in OIC Gen 3

Scenario: Convert Date Format (MM/DD/YYYY → YYYY-MM-DD)

Step 1 – Create Integration

• Login to OIC Gen 3
• Go to Integrations → Create → App Driven Orchestration

Step 2 – Configure Trigger

• Add REST Adapter
• Accept input:

Step 3 – Add Assign or Mapper

Open Mapper and use expression builder.

Step 4 – Use XPath Function

Use:

Explanation:

Step 5 – Save and Activate

• Validate integration
• Activate
• Test via REST client

Common Date-Time Functions in OIC

1. Current Date

👉 Returns system date

2. Current Date Time

👉 Includes timestamp

3. Format DateTime

👉 Converts into readable format

4. Extract Year

5. Add Days

👉 Adds 5 days

6. Timezone Conversion

👉 Converts to IST

Testing the Technical Component

Sample Input

Expected Output

Validation Checklist

• Correct format applied
• No parsing errors
• Timezone handled correctly
• Compatible with target system

Common Errors and Troubleshooting

1. Invalid Date Format

Error:

Cause: Incorrect input format

Fix: Ensure proper substring logic

2. Timezone Issues

Problem: Date shifts unexpectedly

Cause: UTC vs Local mismatch

Fix: Use adjust-dateTime-to-timezone()

3. Null Values

Problem: Integration fails when date is null

Fix: Use conditional mapping:

4. Incorrect Pattern

Problem: Wrong output format

Fix: Use correct pattern:

Best Practices from Real Projects

1. Always Standardize to ISO Format First

Convert everything to:

Then transform further if needed.

2. Handle Timezones Explicitly

Never assume system timezone.

3. Use Reusable Mapping Logic

Create:

• Reusable integrations
• Lookup-based formats

4. Validate Input Early

Add validation before mapping:

• Avoid runtime failures

5. Log Date Transformations

Helps in debugging:

• Especially in production issues

Real Consultant Tip

In one ERP integration project, invoice uploads were failing intermittently. Root cause:

👉 Date format mismatch (DD/MM/YYYY vs MM/DD/YYYY)

Fix:

• Standardized all dates to ISO format inside OIC
• Then converted to ERP-specific format

Result:

✔ Zero failures post-fix
✔ Improved processing reliability

Summary

Handling date-time formatting in Oracle Integration Cloud (OIC Gen 3) is a core skill for any integration consultant. Almost every integration—whether HCM, ERP, or external systems—requires precise handling of date formats and time zones.

Key takeaways:

• Use XPath functions like format-dateTime()
• Always normalize date formats
• Handle timezone conversions carefully
• Validate inputs before transformation
• Test thoroughly with real payloads

For deeper reference, explore official Oracle documentation:
https://docs.oracle.com/en/cloud/saas/index.html

FAQs

1. What is the most commonly used date format in OIC?

The most commonly used format is:

It is ISO standard and compatible with most systems.

2. How do I convert UTC to IST in OIC?

Use:

3. Why does my date shift by one day?

This usually happens due to:

• Timezone differences
• Midnight conversions

Always handle timezone explicitly.