Preface
The Transformation Language
Constants
Operators
Variables
- Built-in Variables
  - SYSDATE
    - Example
- Local Variables
Dates
Functions
- Function Categories
  - Aggregate Functions
  - Aggregate Functions and Nulls
    - Filter Conditions
  - Character Functions
  - Complex Functions
  - Conversion Functions
  - Data Cleansing Functions
  - Date Functions
  - Encoding Functions
  - Financial Functions
  - Numeric Functions
  - Scientific Functions
  - Special Functions
  - String Functions
  - Test Functions
  - Window Functions
- ABORT
- ABS
- ADD_TO_DATE
- AES_DECRYPT
- AES_ENCRYPT
- ANY
- ARRAY
- ASCII
- AVG
- CAST
- CEIL
- CHOOSE
- CHR
- CHRCODE
- COLLECT_LIST
- COLLECT_MAP
- COMPRESS
- CONCAT
- CONCAT_ARRAY
- CONVERT_BASE
- COS
- COSH
- COUNT
- CRC32
- CREATE_TIMESTAMP_TZ
- CUME
- DATE_COMPARE
- DATE_DIFF
- DEC_BASE64
- DECODE
- DECOMPRESS
- ENC_BASE64
- ERROR
- EXP
- EXTRACT_STRUCT
- FIRST
- FLOOR
- FV
- GET_DATE_PART
- GET_TIMEZONE
- GET_TIMESTAMP
- GREATEST
- IIF
- IN
- INDEXOF
- INITCAP
- INSTR
- ISNULL
- IS_DATE
- IS_NUMBER
- IS_SPACES
- LAG
- LAST
- LAST_DAY
- LEAD
- LEAST
- LENGTH
- LN
- LOG
- LOWER
- LPAD
- LTRIM
- MAKE_DATE_TIME
- MAP
- MAP_FROM_ARRAYS
- MAP_KEYS
- MAP_VALUES
- MAX (Dates)
- MAX (Numbers)
- MAX (String)
- MD5
- MEDIAN
- METAPHONE
- MIN (Dates)
- MIN (Numbers)
- MIN (String)
- MOD
- MOVINGAVG
- MOVINGSUM
- NPER
- PARSE_JSON
- PARSE_XML
- PERCENTILE
- PMT
- POWER
- PV
- RAND
- RATE
- REG_EXTRACT
- REG_MATCH
- REG_REPLACE
- REPLACECHR
- REPLACESTR
- RESPEC
- REVERSE
- ROUND (Dates)
- ROUND (Numbers)
- RPAD
- RTRIM
- SET_DATE_PART
- SIGN
- SIN
- SINH
- SIZE
- SOUNDEX
- SQL_LIKE
- SQRT
- STDDEV
- STRUCT
- STRUCT_AS
- SUBSTR
- SUM
- SYSTIMESTAMP
- TAN
- TANH
- TIME_RANGE
- TO_BIGINT
- TO_CHAR (Dates)
- TO_CHAR (Numbers)
- TO_DATE
- TO_DECIMAL
- TO_DECIMAL38
- TO_FLOAT
- TO_INTEGER
- TO_TIMESTAMP_TZ
- TRUNC (Dates)
- TRUNC (Numbers)
- UPPER
- UUID4
- UUID_UNPARSE
- VARIANCE

Transformation Language Reference

10.5.4
- 10.5.6
- 10.5.1
- 10.5
- 10.4.0

Back Next

Default Date Format

The

Data 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 in the data viewer configuration. By default, the date format is MM/DD/YYYY HH24:MI:SS.US.

Because Informatica stores dates in binary format, the

Data 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

Data Integration Service

converts the date to a string in the date format defined in the data viewer configuration.

Convert a string to a date by connecting a string port to a date/time port.
The

Data Integration Service

expects the string values to be in the date format defined by the data viewer configuration. If an input value does not match this format, or if it is an invalid date, the

Data Integration Service

skips the row. If the string is in this format, the

Data 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

Data Integration Service

returns the string in the date format defined in the data viewer configuration. If you specify a format string, the

Data 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

Data Integration Service

expects the string in the date format defined in the data viewer configuration. If you specify a format string, the

Data 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

Dates Overview

Download Guide

Watch

Comments

Communities

Knowledge Base

Success Portal

0 COMMENTS

We’d like to hear from you! Log in to comment.

Rename Saved Search

Table of Contents

Transformation Language Reference

Transformation Language Reference

Default Date Format

Default Date Format