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
LetterDate or Time ComponentExamples
GEra designatorAD
yYear1996; 96
MMonth in yearJuly; Jul; 07
wWeek in year27
WWeek in month2
DDay in year189
dDay in month10
FDay of week in month2
EDay in weekTuesday; Tue
aAm/pm markerPM
HHour in day (0-23)0
kHour in day (1-24)24
KHour in am/pm (0-11)0
hHour in am/pm (1-12)12
mMinute in hour30
sSecond in minute55
SMillisecond978
zTime zonePacific Standard Time; PST; GMT-08:00
ZTime zone-0800

 

Support Contacts

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