When analyzing data it is frequently necessary to ingest data that contains string representations of date values. Unfortunately, the format of the date values can vary depending on the source of the data and sometimes the preference of the person who entered the date! Regional variations in the accepted date format can also result in differences in the format of the data.
When the date values are 'tidy' - that is, where they have a consistent format for all records in a particular field - the conversion of the strings representing the dates can often be handled by standard Dataverse nodes such as the Data Converter node.
However, when the data format of the date values is 'untidy' then the date format detection mechanism will not identify a consistent format, resulting in the data remaining as a string type field.
The attached 'Date Conversion' node is a Community custom node that enables you to convert date values in a selected String type field into Date type values. The node is able to handle untidy date values with multiple formats. You can set the node's 'Region' property to assume European style date formats [DD/MM/CCYY e.g. 22/03/2017] or use the (default) North American style date formats [MM/DD/CCYY e.g. 03/22/2017].
In addition to handling the ISO-standard canonical date format [CCYY-MM-DD] and common regional formats, the node can identify colloquial date formats [e.g. March 22, 2017 or Mar 22, 2017] and less common date formats [e.g. January 1990, Jan 1990 or just 1999].
Where the node could not determine the format of a date value, the record is output to the node's second output pin.
I hope you find it useful!
You will need Dataverse 3.1.5 or later to import the custom library. When the node has been imported you will find it in the 'Aggregation and Transformation' category of the node library.
Please note: This is a Community custom node so it is not part of the Dataverse product and is not covered by the Lavastorm support policy.
Attached files
Date Conversion - 16 Nov 2017.lna