Chapter 12: Working with Date Functions in Fast Formulas

Objective of This Chapter

This chapter teaches you how to use date functions within Oracle Fusion Fast Formulas to perform operations like calculating tenure, finding difference between dates, or formatting dates.

Why Are Date Functions Important?

To calculate employee service duration
To check eligibility based on date of joining
To automate payroll adjustments and benefit calculations
To validate age-based eligibility

Commonly Used Date Functions

Function	Purpose
`GET_DATE_DIFF`	Get difference between two dates
`ADD_DAYS_TO_DATE`	Add days to a specific date
`SYSDATE`	Current system date
`TO_CHAR_DATE`	Convert date to text format
`TO_DATE`	Convert text to date format

Syntax and Examples

1. Calculating Tenure (Service Duration)

 
TENURE_IN_DAYS = GET_DATE_DIFF (SYSDATE, DATE_OF_JOINING)

Explanation:
This calculates the number of days between the employee's joining date and today.

2. Adding Days to a Date

 
NEW_DATE = ADD_DAYS_TO_DATE(DATE_OF_JOINING, 180)

Explanation:
This adds 180 days to the employee's date of joining.

3. Converting Date to Text

 
DATE_TEXT = TO_CHAR_DATE(SYSDATE, 'YYYY-MM-DD')

Explanation:
This converts today's date into a text string in "Year-Month-Day" format.

4. Converting Text to Date

 
CONVERTED_DATE = TO_DATE('2024-05-01', 'YYYY-MM-DD')

Explanation:
This converts a string into a real date value.

Practical Example

Scenario

Check if an employee has completed 1 year. If yes, assign "Eligible for Bonus", otherwise "Not Eligible".

Formula Example

 
INPUTS ARE DATE_OF_JOINING

DEFAULT FOR DATE_OF_JOINING IS '1951/01/01'

TENURE_IN_DAYS = GET_DATE_DIFF(SYSDATE, DATE_OF_JOINING)

IF TENURE_IN_DAYS >= 365 THEN
  BONUS_ELIGIBILITY = 'Eligible for Bonus'
ELSE
  BONUS_ELIGIBILITY = 'Not Eligible'

RETURN BONUS_ELIGIBILITY

Important Points to Remember

Always provide default dates to avoid formula errors.
Use correct date formats when converting.
Always test date logic properly because timezone differences may affect calculations.

Mini Practice Quiz

What function is used to find the difference between two dates?
Answer: GET_DATE_DIFF
Which function gives today's date in Fast Formula?
Answer: SYSDATE
How do you convert a text to date?
Answer: Using TO_DATE function.

Fusion Katta

Pages

Oracle Fast Formulas - Chapter 12: Working with Date Functions in Fast Formulas