Introducing Radical.sh

Forget Code launches a powerful code generator for building API's

Date Format in Informatica

  1. Forget Code
  2. Informatica
  3. Date Format
Informatica uses following formats for dates.

Format StringDescription
D, DD, DDD, DAY, DY, JDays (01-31). Use any of these format strings to specify the entire day portion of a date. For example, if you pass 12-APR-1997 to a date function, use any of these format strings specify 12.
HH, HH12, HH24Hour of day (0-23), where 0 is 12 AM (midnight). Use any of these formats to specify the entire hour portion of a date. For example, if you pass the date 12-APR-1997 2:01:32 PM, use HH, HH12, or HH24 to specify the hour portion of the date.
MIMinutes (0-59).
MM, MON, MONTHMonth (01-12). Use any of these format strings to specify the entire month portion of a date. For example, if you pass 12-APR-1997 to a date function, use MM, MON, or MONTH to specify APR.
MSMilliseconds (0-999).
NSNanoseconds (0-999999999).
SS, SSSSSeconds (0-59).
USMicroseconds (0-999999).
Y, YY, YYY, YYYY, RRYear portion of date (1753 to 9999). Use any of these format strings to specify the entire year portion of a date. For example, if you pass 12-APR-1997 to a date function, use Y, YY, YYY, or YYYY to specify 1997.

Date format is not case-sensitive and it has to be enclosed with single quote.

The above date format is applicable to following functions,
FunctionDescription
ADD_TO_DATEAdd or subtract months, days, years to a date
DATE_DIFFCalculate the difference between two dates in days, months or years.
GET_DATE_PARTExtract date or month or year or time part from a date
IS_DATETo check given string is a date or notAlso check IS_DATE date formats
ROUNDThe part of the date you want to round.
SET_DATE_PARTThe part of the date you want to change.
SYSTIMESTAMPThe timestamp precision.
TO_CHAR (Dates)The character string.Also check TO_CHAR date formats
TO_DATEThe character string.Also check TO_DATE date formats
TRUNC (Dates)The part of the date you want to truncate.