Taskflows > Taskflow functions > toDate
  

toDate

Converts a character string to a Date/Time data type. You use the toDate format strings to specify the format of the source strings.
The output port must be Date/Time for toDate expressions.
If you are converting two-digit years with toDate, use either the RR or YY format string. Do not use the YYYY format string.

Syntax

date:toDate(xs:dateTime('date'), 'format')
Note: You must manually add the xs:dateTime phrase and enclose the date values within single quotation marks.
The following table describes the arguments:
Argument
Required/
Optional
Description
date
Required
Must be a string data type. Passes the values that you want to convert to dates. You can enter any valid transformation expression.
format
Required
Enter a valid toDate format string. The format string must match the parts of the dare argument. For example, if you pass the date 'Mar 15 1998 12:43:10AM', you must use the format string 'MON DD YYYY HH12:MI:SSAM'.

Return Value

Date.
The toDate function always returns a date and time. If you pass a string that does not have a time value, the date returned always includes the time 00:00:00.000000000. You can map the results of this function to any target column with a datetime data type.
NULL if you pass a NULL value to this function.

Examples

date:toDate(xs:dateTime('DATE_PROMISED'), 'MM/DD/YY')
The following table lists some sample values and return values:
DATE_PROMISED
RETURN VALUE
'01/22/98'
Jan 22 1998 00:00:00
'05/03/98'
May 3 1998 00:00:00
'11/10/98'
Nov 10 1998 00:00:00
'10/19/98'
Oct 19 1998 00:00:00
NULL
NULL
date:toDate(xs:dateTime('DATE_PROMISED'), 'MON DD YYYY HH12:MI:SSAM')
The following table lists some sample values and return values:
DATE_PROMISED
RETURN VALUE
'Jan 22 1998 02:14:56PM'
Jan 22 1998 02:14:56PM
'Mar 15 1998 11:11:11AM'
Mar 15 1998 11:11:11AM
'Jun 18 1998 10:10:10PM'
Jun 18 1998 10:10:10PM
'October 19 1998'
Error. Integration Service skips this row.
NULL
NULL
The following expression converts strings in the SHIP_DATE_MJD_STRING port to date values:
date:toDate(xs:dateTime('SHIP_DATE_MJD_STR'), 'J')
The following table lists some sample values and return values:
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.
The following expression converts a string to a four-digit year format. The current year is 1998:
date:toDate(xs:dateTime('DATE_STR'), 'MM/DD/RR')
The following table lists some sample values and return values:
DATE_STR
RETURN VALUE
'04/01/98'
04/01/1998 00:00:00.000000000
'08/17/05'
08/17/2005 00:00:00.000000000
The following expression converts a string to a four-digit year format. The current year is 1998:
date:toDate(xs:dateTime('DATE_STR'), 'MM/DD/YY')
The following table lists some sample values and return values:
DATE_STR
RETURN VALUE
'04/01/98'
04/01/1998 00:00:00.000000000
'08/17/05'
08/17/1905 00:00:00.000000000
The following expression converts a string to a four-digit year format. The current year is 1998:
date:toDate(xs:dateTime('DATE_STR'), 'MM/DD/Y')
The following table lists some sample values and return values:
DATE_STR
RETURN VALUE
'04/01/8'
04/01/1998 00:00:00.000000000
'08/17/5'
08/17/1995 00:00:00.000000000
The following expression converts a string to a four-digit year format. The current year is 1998:
date:toDate(xs:dateTime('DATE_STR'), 'MM/DD/YYY')
The following table lists some sample values and return values:
DATE_STR
RETURN VALUE
'04/01/998'
04/01/1998 00:00:00.000000000
'08/17/995'
08/17/1995 00:00:00.000000000
The following expression converts strings that includes the seconds since midnight to date values:
date:toDate(xs:dateTime('DATE_STR'), 'MM/DD/YYYY SSSSS')
The following table lists some sample values and return values:
DATE_STR
RETURN_VALUE
'12/31/1999 3783'
12/31/1999 01:02:03
'09/15/1996 86399'
09/15/1996 23:59:59