Function Reference > Dates > Dates overview
  

Dates overview

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 that hosts Data Integration.
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

The transformation language provides a set of generic datatypes to transform data from different sources. These transformation datatypes include a Date/Time datatype. Data Integration 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: Data Integration supports dates between 1753 A.D. and 9999 A.D.

Milliseconds

Data Integration supports datetime values up to the second.
If you import a datetime value that includes milliseconds, Data Integration truncates to seconds. If you write a datetime value to a target column that supports milliseconds, Data Integration inserts zeros for the millisecond portion of the date.

Julian Day, Modified Julian Day, and the Gregorian calendar

Data Integration 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 Data Integration. 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: Data Integration 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. Data Integration 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, Data Integration returns the current century plus the two-digit year from the source string. If the source string year is between 50 and 99, Data Integration 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, Data Integration returns the next century plus the two-digit year from the source string. If the source string year is between 50 and 99, Data Integration 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

Although date formats vary from database to database, and even between applications, Data Integration can read any date with a date datatype.
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.

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 Data Integration stores dates in binary format, Data Integration only uses the default date format in certain circumstances.
Data Integration only uses the default date format when you perform the following actions:
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, Data Integration skips the row. If the string is in the default date format, Data Integration converts the string to a date value.
Use TO_CHAR(date, [format_string]) to convert dates to strings.
If you omit the format string, Data Integration returns the string in the default date format, MM/DD/YYYY HH24:MI:SS. If you specify a format string, Data Integration returns a string in the specified format.
Use TO_DATE(date, [format_string]) to convert strings to dates.
If you omit the format string, Data Integration expects the string in the default date format, MM/DD/YYYY HH24:MI:SS. If you specify a format string, Data Integration expects a string in the specified format.
The default date format of MM/DD/YYYY HH24:MI:SS consists of: