Dates Overview
The transformation language provides a set of date functions and built-in date variables to perform transformations on dates. With the date functions, you can round, truncate, or compare dates, extract one part of a date, or perform arithmetic on a date. You can pass any value with a date datatype to a date function.
Use date variables to capture the current date or session start time on the node hosting the Data Integration Service.
The transformation language also provides the following sets of format strings:
- •Date format strings. Use with date functions to specify the parts of a date.
- •TO_CHAR format strings. Use to specify the format of the return string.
- •TO_DATE and IS_DATE format strings. Use to specify the format of a string you want to convert to a date or test.
Date/Time Datatype
Informatica uses generic datatypes to transform data from different sources. These transformation datatypes include a Date/Time datatype that supports datetime values up to the nanosecond. Informatica stores dates internally in binary format.
Date functions accept datetime values only. To pass a string to a date function, first use TO_DATE to convert it to a datetime value. For example, the following expression converts a string port to datetime values and then adds one month to each date:
ADD_TO_DATE( TO_DATE( STRING_PORT, 'MM/DD/RR'), 'MM', 1 )
You can use dates between 1 A.D. and 9999 A.D in the Gregorian calendar system.
Julian Day, Modified Julian Day, and the Gregorian Calendar
You can use dates in the Gregorian calendar system only. Dates in the Julian calendar are called Julian dates and are not supported in Informatica. This term should not be confused with Julian Day or with Modified Julian Day.
You can manipulate Modified Julian Day (MJD) formats using the J format string. The MJD for a given date is the number of days to that date since Jan 1 4713 B.C. 00:00:00 (midnight). By definition, MJD includes a time component expressed as a decimal, which represents some fraction of 24 hours. The J format string does not convert this time component.
For example, the following TO_DATE expression converts strings in the SHIP_DATE_MJD_STRING port to date values in the default date format:
TO_DATE (SHIP_DATE_MJD_STR, 'J')
SHIP_DATE_MJD_STR | RETURN_VALUE |
|---|
2451544 | Dec 31 1999 00:00:00.000000000 |
2415021 | Jan 1 1900 00:00:00.000000000 |
SHIP_DATE_MJD_STR | RETURN_VALUE |
|---|
2451544 | Dec 31 1999 00:00:00.000000000 |
2415021 | Jan 1 1900 00:00:00.000000000 |
Because the J format string does not include the time portion of a date, the return values have the time set to 00:00:00.000000000.
You can also use the J format string in TO_CHAR expressions. For example, use the J format string in a TO_CHAR expression to convert date values to MJD values expressed as strings. For example:
TO_CHAR(SHIP_DATE, 'J')
SHIP_DATE | RETURN_VALUE |
|---|
Dec 31 1999 23:59:59 | 2451544 |
Jan 1 1900 01:02:03 | 2415021 |
Dates in the Year 2000
All transformation language date functions support the year 2000. Informatica Developer supports dates between 1 A.D. and 9999 A.D.
RR Format String
The transformation language provides the RR format string to convert strings with two-digit years to dates. Using TO_DATE and the RR format string, you can convert a string in the format MM/DD/RR to a date. The RR format string converts data differently depending on the current 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 Data 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.
- •Current Year Between 50 and 99. If the current year is between 50 and 99 (such as 1998) and the source string year is between 0 and 49, the Data Integration Service returns the next century plus the two-digit year from the source string. If the source string year is between 50 and 99, the Data Integration Service returns the current century plus the specified two-digit year.
The following table summarizes how the RR format string converts to dates:
Current year | Source year | RR Format String Returns |
|---|
0-49 | 0-49 | Current century |
0-49 | 50-99 | Previous century |
50-99 | 0-49 | Next century |
50-99 | 50-99 | Current century |
Example
The following expression produces the same return values for any current year between 1950 and 2049:
TO_DATE( ORDER_DATE, 'MM/DD/RR' )
ORDER_DATE | RETURN_VALUE |
|---|
'04/12/98' | 04/12/1998 00:00:00.000000000 |
'11/09/01' | 11/09/2001 00:00:00.000000000 |
Difference Between the YY and RR Format Strings
Informatica Developer also provides a YY format string. Both the RR and YY format strings specify two-digit years. The YY and RR format strings produce identical results when used with all date functions except TO_DATE. In TO_DATE expressions, RR and YY produce different results.
The following table shows the different results each format string returns:
String | Current Year | TO_DATE(String, ‘MM/DD/RR’) | TO_DATE(String, ‘MM/DD/YY’) |
|---|
04/12/98 | 1998 | 04/12/1998 00:00:00.000000000 | 04/12/1998 00:00:00.000000000 |
11/09/01 | 1998 | 11/09/2001 00:00:00.000000000 | 11/09/1901 00:00:00.000000000 |
04/12/98 | 2003 | 04/12/1998 00:00:00.000000000 | 04/12/2098 00:00:00.000000000 |
11/09/01 | 2003 | 11/09/2001 00:00:00.000000000 | 11/09/2001 00:00:00.000000000 |
For dates in the year 2000 and beyond, the YY format string produces less meaningful results than the RR format string. Use the RR format string for dates in the twenty-first century.
Dates in Relational Databases
In general, dates stored in relational databases contain a date and time value. The date includes the month, day, and year, while the time might include the hours, minutes, seconds, and sub-seconds. You can pass datetime data to any of the date functions.
Dates in Flat Files
Use the TO_DATE function to convert strings to datetime values. You can also use IS_DATE to check if a string is a valid date before converting it with TO_DATE. The transformation language date functions accept date values only. To pass a string to a date function, you must first use the TO_DATE function to convert it to a transformation Date/Time datatype.
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 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 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 session configuration object.
- •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 session configuration object. 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 session properties. 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 session properties. 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