TO_DATE and IS_DATE Formats in Informatica

To_date and is_date use the same formats as shown in the below table
Format StringDescription
AM, a.m., PM, p.m.Meridian indicator. Use any of these format strings to specify AM and PM hours. AM and PM return the same values as do a.m. and p.m.
DAYName of day, including up to nine characters (for example, Wednesday). The DAY format string is not case sensitive.
DDDay of month (1-31).
DDDDay of year (001-366, including leap years).
DYAbbreviated three-character name for a day (for example, Wed). The DY format string is not case sensitive.
HH, HH12Hour of day (1-12).
HH24Hour of day (0-23), where 0 is 12AM (midnight).
JModified Julian Day. Convert strings in MJD format to date values. It ignores the time component of the source string, assigning all dates the time of 00:00:00.000000000. For example, the expression TO_DATE(‘2451544’, ‘J’) converts 2451544 to Dec 31 1999 00:00:00.000000000.
MIMinutes (0-59).
MMMonth (1-12).
MONTHName of month, including up to nine characters (for example, August). Case does not matter.
MONAbbreviated three-character name for a month (for example, Aug). Case does not matter.
MSMilliseconds (0-999).
NSNanoseconds (0-999999999).
RRFour-digit year (for example, 1998, 2034). Use when source strings include two-digit years. Use with TO_DATE to convert two-digit years to four-digit years.
- Current Year Between 50 and 99. If the current year is between 50 and 99 (such as 1998) and the year value of the source string is between 0 and 49, the Integration Service returns the next century plus the two-digit year from the source string. If the year value of the source string is between 50 and 99, the Integration Service returns the current century plus the specified two-digit year.
- Current Year Between 0 and 49. If the current year is between 0 and 49 (such as 2003) and the source string year is between 0 and 49, the Integration Service returns the current century plus the two-digit year from the source string. If the source string year is between 50 and 99, the Integration Service returns the previous century plus the two-digit year from the source string.
SSSeconds (0-59).
SSSSSSeconds since midnight. When you use SSSSS in a TO_DATE expression, the Integration Service only evaluates the time portion of a date.
For example, the expression TO_DATE( DATE_STR, ‘MM/DD/YYYY SSSSS’) converts 12/31/1999 3783 to 12/31/1999 01:02:03.
USMicroseconds (0-999999).
YThe current year on the machine running the Integration Service with the last digit of the year replaced with the string value.
YYThe current year on the machine running the Integration Service with the last two digits of the year replaced with the string value.
YYYThe current year on the machine running the Integration Service with the last three digits of the year replaced with the string value.
YYYYFour digits of a year. Do not use this format string if you are passing two-digit years. Use the RR or YY format string instead.