Dates Overview
The appendix provides information about using 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 machine hosting Informatica Cloud.
The Informatica Cloud 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
The transformation language provides a set of generic datatypes to transform data from different sources. These transformation datatypes include a Date/Time datatype. Informatica Cloud 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 field to datetime values and then adds one month to each date:
ADD_TO_DATE( TO_DATE( STRING_PORT, 'MM/DD/RR'), 'MM', 1 )
Note: Informatica Cloud supports dates between 1753 A.D. and 9999 A.D.
Milliseconds
Informatica Cloud supports datetime values up to the second. If you import a datetime value that includes milliseconds, Informatica Cloud truncates to seconds. If you write a datetime value to a target column that supports milliseconds, Informatica Cloud inserts zeros for the millisecond portion of the date.
Julian Day, Modified Julian Day, and the Gregorian Calendar
Informatica Cloud supports dates in the Gregorian calendar system only. Dates expressed in a different calendar system are not supported.
Note: Dates in the Julian calendar are called Julian dates and are not supported in Informatica Cloud. This term should not be confused with Julian Day or with Modified Julian Day.
The transformation language provides the ability to 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 BC 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 field 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 |
2415021 | Jan 1 1900 00:00:00 |
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.
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 |
Note: Informatica Cloud ignores the time portion of the date in a TO_CHAR expression.
Dates in the Year 2000
All transformation language date functions support the year 2000. Informatica Cloud supports dates between 1753 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, Informatica Cloud returns the current century plus the two-digit year from the source string. If the source string year is between 50 and 99, Informatica Cloud 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, Informatica Cloud returns the next century plus the two-digit year from the source string. If the source string year is between 50 and 99, Informatica Cloud 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 of RR
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 |
'11/09/01' | 11/09/2001 00:00:00 |
Difference Between the YY and RR Format Strings
The transformation language 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 | 04/12/1998 00:00:00 |
11/09/01 | 1998 | 11/09/2001 00:00:00 | 11/09/1901 00:00:00 |
04/12/98 | 2003 | 04/12/1998 00:00:00 | 04/12/2098 00:00:00 |
11/09/01 | 2003 | 11/09/2001 00:00:00 | 11/09/2001 00:00:00 |
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 Databases
In general, dates stored in databases contain a date and time value. The date includes the month, day, and year, while the time might include the hours, minutes, and seconds. You can pass datetime data to any of the date functions. Although date formats vary from database to database, and even between applications, Informatica Cloud can read any date with a date datatype.
Dates in Flat Files
The transformation language provides 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.
Note: Transformation language date functions accept date values only. If you want 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 application uses a default date format to store and manipulate strings that represent dates. Because Informatica Cloud stores dates in binary format, Informatica Cloud only uses the default date format when you:
- •Convert a date to a string by connecting a date/time field to a string field. The application converts the date to a string in the default date format, MM/DD/YYYY HH24:MI:SS.
- •Convert a string to a date by connecting a string field to a date/time field. The application expects the string values to be in the default date format, MM/DD/YYYY HH24:MI:SS. If an input value does not match this format, or it is an invalid date, Informatica Cloud skips the row. If the string is in the default date format, Informatica Cloud converts the string to a date value.
- •Use TO_CHAR(date, [format_string]) to convert dates to strings. If you omit the format string, Informatica Cloud returns the string in the default date format, MM/DD/YYYY HH24:MI:SS. If you specify a format string, Informatica Cloud returns a string in the specified format.
- •Use TO_DATE(date, [format_string]) to convert strings to dates. If you omit the format string, Informatica Cloud expects the string in the default date format, MM/DD/YYYY HH24:MI:SS. If you specify a format string, Informatica Cloud expects a string in the specified format.
The default date format of MM/DD/YYYY HH24:MI:SS 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