Convert String to Date

1.3.10.11 Convert String to Date

The Convert String to Date transformer takes values in a String or Array attribute, recognizes them using a reference list of date formats, and attempts to convert them to a standard Date or Date Array type.

Use Convert String to Date when you have date values held in a String or String Array attribute and want to perform date-specific processing on them - for example to run them through a Date Profiler.

To find date values in a String attribute, run the Data Types Profiler. To isolate records that do not have the expected data type in a given attribute, run a Data Type Check.

Note:

The Convert String to Date processor itself can do this in isolation, as it will "fail to convert"; that is, convert as null, any values that are not recognized as dates using its reference list. This includes 'invalid dates', for example dates with day values in the month part of the date.

To convert a String or String Array attribute into the Date or Date Array type, the processor needs to recognize date values correctly. This is achieved using a reference list with a date format or formats. The understood format of the date may be locale-specific - for example you may want to convert 01/04/2001 to 1st-Apr-2001 if the date was captured in a UK format, or to 4th-Jan-2001 if the date was captured in a US format. In order to recognize the dates correctly, the correct format of the date must be used in the reference list. A default list of formats is included with EDQ. If the format you want to recognize is not found in this list, you can create your own list with a date format that will be recognized by the standard Java API, and thus by the date processors in EDQ - see http://java.sun.com/javase/6/docs/api/java/text/SimpleDateFormat.html.

The following table describes the configuration options:

Configuration	Description
Inputs	Specify one or more String or String Array attributes for conversion to a Date or Date Array type. If multiple attributes are submitted for conversion and one fails the entire record is marked as having failed, although the valid attributes will be correctly converted.
Options	Specify the following options: `List of recognized date formats`: recognizes dates in a variety of different formats. The reference list is checked in order, so dates are recognized according to the first matching row in the list. Specified as Reference Data (Date Formatting Category). Default value: `*Date Formats`. `Time zone`: the time zone associated with the input string value. The date will be produced by converting from this time zone to the UTC time zone. The output date value does not have an associated time zone, as all dates are stored as UTC values. Specified as a time zone. Default value is the Director Time Zone.
Outputs	Describes any data attribute or flag attribute outputs.
Data Attributes	The following data attributes are output: `[Attribute Name].StringToDate`: stores the result of the String to Date conversion. Value is derived from the original attribute value, converted to a Date or Date Array type.
Flags	The following flags are output: `StringToDateSuccess`: flags whether or not the String to Date conversion was successful for all records. Possible values: `Y`/`N`.

The following table describes the statistics produced by the profiler:

Statistic	Description
Success	The number of records where the String to DATE conversion was successful.
Failure	The number of records where the String to DATE conversion was unsuccessful. This can occur because the input value was not recognized as a date (at all), or because it was recognized as an invalid date (such as a date that did not occur, such as 29th February in a year that is not a leap year).

Statistic

Description

Success

The number of records where the String to DATE conversion was successful.

Failure

The number of records where the String to DATE conversion was unsuccessful.

This can occur because the input value was not recognized as a date (at all), or because it was recognized as an invalid date (such as a date that did not occur, such as 29th February in a year that is not a leap year).

Click the Additional Information button to show the above statistics as percentages.

Output Filters

The following output filters are available:

Records where conversion was successful
Records where conversion was unsuccessful

Example

The Customers table has a DT_PURCHASED attribute that is stored as a String, that is, not as a controlled DATE attribute.

Using the standard *Date Formats reference list to recognize date values, these are the results of the String to DATE conversion:

Success	Failure
2003 99.7%	7 0.3%

You can drill down into the success or failure.

Note:

Where a value is not recognized as a date because it is null, or because it contains a value that is not recognized as a date, the converted DATE value is null.