Date And Time Normalization

Purpose

• Read in dates and timestamps of any input format, including text, and output as a date.
• Normalize different dates and timestamps from different sources to the same output format.

Category Location: All, Calculate or manipulate, Clean or normalize

Field Description

1. Attribute to normalize: Input date field to normalize.
2. Write to: Output date field.
3. Format to output – Choose from 8 existing formats or create a custom format. If you use a custom format, refer to the table below for format patterns.
4. Source Format – if the input attribute is type Text, supply the source format so Openprise knows how to interpret the date in the text field. This option is in the Advanced Configuration section.

Tips

• Only string attributes allow you to specify the input format.  You can change the output format when writing to both string and date type attributes.
• Openprise recommends that you keep dates or timestamps of non-standard format, for example "29.01.2016 T01.32.30", as a string type attribute in the input data source and use Date and Time Normalization task with a source format, such as "dd.MM.yyyy THH.mm.ss", and write the normalized format, such as "MM/dd/yyyy HH:mm:ss", into a date type attribute.

Examples

• Tradeshow lists from Europe that use the "dd/MM/yyyy" format may need to be normalized to "MM/dd/yyyy"
• Custom formats are case dependent and use the following conventions

**Pay special attention to the casing of the letters**