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.

 

Field Description

 

  • 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.

 

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
Letter Date or Time Component Examples
G Era designator AD
y Year 1996; 96
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
a Am/pm marker PM
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
S Millisecond 978
z Time zone Pacific Standard Time; PST; GMT-08:00
Z Time zone -0800

 

Support Contacts

If you have any additional questions, please feel free to contact us at support@openprisetech.com.