Date and Time Normalization
- 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.
- Attribute to normalize – Input date field to normalize.
- Write to – Output date field.
- 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.
- 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.
- 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.
- 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
|Letter||Date or Time Component||Examples|
|M||Month in year||July; Jul; 07|
|w||Week in year||27|
|W||Week in month||2|
|D||Day in year||189|
|d||Day in month||10|
|F||Day of week in month||2|
|E||Day in week||Tuesday; Tue|
|H||Hour in day (0-23)||0|
|k||Hour in day (1-24)||24|
|K||Hour in am/pm (0-11)||0|
|h||Hour in am/pm (1-12)||12|
|m||Minute in hour||30|
|s||Second in minute||55|
|z||Time zone||Pacific Standard Time; PST; GMT-08:00|
If you have any additional questions, please feel free to contact us at firstname.lastname@example.org.