Table of Contents

Search

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

Transformation Language Reference

Transformation Language Reference

TO_DATE and IS_DATE Format Strings

TO_DATE and IS_DATE Format Strings

The TO_DATE function converts a string with the format you specify to a datetime value. TO_DATE is generally used to convert strings from flat files to datetime values. TO_DATE format strings are not internationalized and must be entered in the predefined formats.
TO_DATE and IS_DATE use the same set of format strings.
When you create a TO_DATE expression, use a format string for each part of the date in the source string. The source string format and the format string must match. The date separator need not match for date validation to take place. If any part does not match, the
PowerCenter Integration Service
does not convert the string, and it skips the row. If you omit the format string, the source string must be in the date format specified in the session.
IS_DATE indicates whether a value is a valid date. A valid date is any string in the date format specified in the session. If the strings that you want to test are not in the specified date format, use the format of the strings listed in "TO_DATE and IS_DATE Format Strings" table. If the format of a string does not match the specified format or if the string does not represent a valid date, the function returns FALSE (0). If the format of the string matches the specified format of the string and is a valid date, the function returns TRUE (1). IS_DATE format strings are not internationalized and must be entered in one of the formats listed in the following table.
The following table lists the format strings for the functions TO_DATE and IS_DATE:
TO_DATE and IS_DATE Format Strings
Format String
Description
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.
DAY
Name of day, including up to nine characters (for example, Wednesday). The DAY format string is not case sensitive.
DD
Day of month (1-31).
DDD
Day of year (001-366, including leap years).
DY
Abbreviated three-character name for a day (for example, Wed). The DY format string is not case sensitive.
HH, HH12
Hour of day (1-12).
HH24
Hour of day (0-23), where 0 is 12AM (midnight).
J
Modified 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.
MI
Minutes (0-59).
MM
Month (1-12).
MONTH
Name of month, including up to nine characters (for example, August). Case does not matter.
MON
Abbreviated three-character name for a month (for example, Aug). Case does not matter.
MS
Milliseconds (0-999).
NS
Nanoseconds (0-999999999).
RR
Four-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
    PowerCenter 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
    PowerCenter 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
    PowerCenter 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
    PowerCenter Integration Service
    returns the previous century plus the two-digit year from the source string.
SS
Seconds (0-59).
SSSSS
Seconds since midnight. When you use SSSSS in a TO_DATE expression, the
PowerCenter 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.
US
Microseconds (0-999999).
Y
The current year on the node running the
PowerCenter Integration Service
with the last digit of the year replaced with the string value.
YY
The current year on the node running the
PowerCenter Integration Service
with the last two digits of the year replaced with the string value.
YYY
The current year on the node running the
PowerCenter Integration Service
with the last three digits of the year replaced with the string value.
YYYY
Four 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.