Table of Contents

Search

  1. Preface
  2. The Transformation Language
  3. Constants
  4. Operators
  5. Variables
  6. Dates
  7. Functions
  8. Creating Custom Functions
  9. Custom Function API Reference

Transformation Language Reference

Transformation Language Reference

Default Date Format

Default Date Format

The
PowerCenter Integration Service
uses a default date format to store and manipulate strings that represent dates. To specify the default date format, enter a date format in the DateTime Format String attribute on the Confid Object tab for a session or session configuration object. By default, the date format is MM/DD/YYYY HH24:MI:SS.US.
Because Informatica stores dates in binary format, the
PowerCenter Integration Service
uses the default date format when you perform the following actions:
  • Convert a date to a string by connecting a date/time port to a string port.
    The
    PowerCenter Integration Service
    converts the date to a string in the date format defined in the session configuration object.
  • Convert a string to a date by connecting a string port to a date/time port.
    The
    PowerCenter Integration Service
    expects the string values to be in the date format defined by the session configuration object. If an input value does not match this format, or if it is an invalid date, the
    PowerCenter Integration Service
    skips the row. If the string is in this format, the
    PowerCenter Integration Service
    converts the string to a date value.
  • Use TO_CHAR(date, [format_string]) to convert dates to strings.
    If you omit the format string, the
    PowerCenter Integration Service
    returns the string in the date format defined in the session properties. If you specify a format string, the
    PowerCenter Integration Service
    returns a string in the specified format.
  • Use TO_DATE(date, [format_string]) to convert strings to dates.
    If you omit the format string, the
    PowerCenter Integration Service
    expects the string in the date format defined in the session properties. If you specify a format string, the
    PowerCenter Integration Service
    expects a string in the specified format.
The default date format of MM/DD/YYYY HH24:MI:SS.US consists of:
  • Month (January = 01, September = 09)
  • Day (of the month)
  • Year (expressed in four digits, such as 1998)
  • Hour (in 24-hour format, for example, 12:00:00AM = 0, 1:00:00AM = 1, 12:00:00PM = 12, 11:00:00PM = 23)
  • Minutes
  • Seconds
  • Microseconds