Date Functions in Informatica Cloud (IICS)

1. TO_DATE

The TO_DATE function in Informatica Cloud converts a string to a date format. The function always returns a date and time value.  

Syntax

TO_DATE( string [, date_format] )

• The string must be in a valid TO_DATE format.
• If the date_format is not specified, the string value must be in the default date format of MM/DD/YYYY HH24:MI:SS.
• If the string does not have a time value, the date returned always includes the time 00:00:00.

The following example converts strings in various formats to a date/time format using the TO_DATE function.

Note that when the string value passed to the TO_DATE function does not match with the date format specified in the function, the Data Integration writes the row into the error rows file.

2. IS_DATE

The IS_DATE function in Informatica Cloud validates whether a string value is in a valid date format and returns an integer value.

• TRUE (1) if the string is in a valid date format.
• FALSE (0) if the string is not in a valid date format.
• NULL if the string is NULL.

Syntax

IS_DATE( string [, date_format] )

• If the date_format is not specified, the string value must be in the default date format of MM/DD/YYYY HH24:MI:SS to be validated TRUE.

The following expression checks if the string value passed to the IS_DATE function is in default date format.

IS_DATE( HIRE_DATE )

The following expression checks if the string value passed to the IS_DATE function is in YYYY-MM-DD date format.

IS_DATE( HIRE_DATE, 'YYYY-MM-DD' )

When the input string is expected to be in different date formats, the IS_DATE function can be used in conjunction with the TO_DATE function to identify the format of the string and convert it accordingly.

The following expression identifies the two different date formats of the input string using IS_DATE and converts them to date/time data type using the TO_DATE function. It returns NULL when none of the formats match.

IIF( IS_DATE( HIRE_DATE, 'YYYY-MM-DD' ), TO_DATE( HIRE_DATE, 'YYYY-MM-DD' ),
     IIF( IS_DATE( HIRE_DATE, 'YYYY-MM-DD HH24:MI:SS' ), TO_DATE( HIRE_DATE, 'YYYY-MM-DD HH24:MI:SS' ), 
          NULL ))

3. ADD_TO_DATE

The ADD_TO_DATE function in Informatica Cloud adds a specified value to a part of the date/time value and returns a date in the same format as the date you pass to the function.

Syntax

ADD_TO_DATE( date, format, amount )

• date: Date/Time datatype value that you want to change.
• format: Format is the string specifying the portion of the date value you want to change.

• amount: A positive or negative integer value specifying the amount of years, months, days, hours, and so on by which you want to change the date value.

The following expression adds the specified amount value to the format specified in the input date value and returns a modified date value.

HIRE_DATE (Date/Time):  01/31/2023 00:00:00

4. DATE_COMPARE

The DATE_COMPARE function in Informatica Cloud compares two date values and returns an integer value indicating which of the two dates is earlier.

The DATE_COMPARE function returns

• -1 if the first date is earlier.
• 0 if the two dates are equal.
• 1 if the second date is earlier.
• NULL if one of the date values is NULL.

Syntax          

DATE_COMPARE( date1, date2 )

The following expression compares two date values and returns an integer indicating which of the two values is earlier.

DATE_COMPARE( DATE_1,  DATE_2 )

5. DATE_DIFF

The DATE_DIFF function in Informatica Cloud compares two date values and returns the time difference between them. It subtracts the second date value from the first one and returns the difference in the requested format, which can be years, months, days, hours, minutes, or seconds.

Syntax

DATE_DIFF( date1, date2, format )

• date1 and date2 are Date/Time datatype values to be compared.
• format is the string specifying the desired date or time measurement for the return value.

The following expression compares two date values and returns the time difference in all the supported formats.

DATE_1 (Date/Time):  01/31/2023 00:00:00
DATE_2 (Date/Time):  03/15/2023 13:25:55

6. GET_DATE_PART

The GET_DATE_PART function in Informatica Cloud returns the specified part of a date as an integer value.

Syntax

GET_DATE_PART( date, format )

• date: Date/Time datatype value from which you want to extract information.
• format: Format is the string specifying the portion of the date value you want to extract.

The following expression extracts year, month, day, hour, minute, and seconds information from the HIRE_DATE field.

HIRE_DATE (Date/Time):  01/31/2023 13:25:55

7. SET_DATE_PART

The SET_DATE_PART function in Informatica Cloud assigns a specified value to a specific portion of the date/time value provided.

Syntax

SET_DATE_PART( date, format, value )

• date: Date/Time datatype value that you want to change.
• format: Format is the string specifying the portion of the date/time value you want to change.
• value: A positive value to be assigned to the specified portion of the date/time value.

The following expression sets the specified values in the date/time data type field HIRE_DATE.

HIRE_DATE (Date/Time):  01/31/2023 13:25:55

To alter various parts of a date simultaneously, you can nest multiple SET_DATE_PART functions within the date argument. 

The following expression sets the hours, minutes and seconds values of date/time value to 0.

SET_DATE_PART( SET_DATE_PART( SET_DATE_PART( HIRE_DATE, 'HH', 0), 'MI', 0 ), 'SS', 0 )

8. LAST_DAY

The LAST_DAY function in Informatica Cloud returns the date of the last day of the month to which the passed date value belongs.

Syntax

LAST_DAY( date )

The following expression returns the last day of the month to which the HIRE_DATE belongs.

LAST_DAY( HIRE_DATE )

Subscribe to our Newsletter !!

Related Articles: