You are here: Processor Library > Transformation > Convert String to Date

Convert String to Date

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

Use

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

In order 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.

In order to convert a String attribute into the DATE format, 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 OEDQ. If the format you wish 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 OEDQ - see http://java.sun.com/javase/6/docs/api/java/text/DateFormat.html.

Configuration

Inputs

A single String attribute that you wish to convert to a Date data type.

Options

Option

Type

Purpose

Default Value

List of recognized date formats

Reference Data (Date Formatting Category)

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.

*Date Formats

Time zone

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.

Note: The output date value does not have an associated time zone, as all dates are stored as UTC values.

Director Time Zone

Outputs

Data attributes

Data attribute

Type

Purpose

Value

[Attribute Name].StringToDate

Derived

Stores the result of the String -> DATE conversion

The original attribute value, converted to a standard DATE

Flags

Flag attribute

Purpose

Possible Values

[Attribute Name].ConvertToDateSuccess

Indicates which data has been successfully converted

Y/N

Execution

Execution Mode

Supported

Batch

Yes

Real time Monitoring

Yes

Real time Response

Yes

Results Browsing

The Convert String to Date transformer produces the following summary statistics of its processing:

Statistic

Meaning

Success

The number of records where the String -> DATE conversion was successful

Failure

The number of records where the String -> 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)

Additional Information

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

Output Filters

The following output filters are available from the Convert String to Date processor:

Example

The Customers table in the example Service Management data 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 -> DATE conversion:

Summary View

Success Drill-down

Failure Drill-down

Note that 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.

Oracle ® Enterprise Data Quality Help version 9.0
Copyright © 2006,2011 Oracle and/or its affiliates. All rights reserved.