Icon

KNIME_​flexible date conversion

KNIME_Workflow With flexible DATE format mask
Workflow to demonstrate how to create a very flexible date format mask to handle a wide variety of date formats. This works providedthat the supplied day month and year are are a known order.What I wanted was a date handling mask that, given a specific order of day month year, didn't actually care too much about how thecomponent parts of the date were actually written. The format mask can only go so far though, and some string manipulation isrequired to handle the month name being in anything other than Title (Capitalized) case. So if it comes through as lowercase oruppercase it would fail. Likewise, noise punctuation / \ , . -, whatever needs to be turned to white space and duplicated/superfluouswhite space has to be removed in order for Capitalize to work in all the handled situations.Being in the UK I concentrated first on regular UK ordering D M Y. But scroll down and I have done the same with (my understandingof) typical US ordering as M D YIf you have a locale where they are typically in a different order to that given here, change the order in which d M and y entries appearin the date mask.15/04/2021 @takbb Brian Bates This workflow will correctly handle all of thefollowing (typical UK) date formats:i.e. provided that it appears as day month yearf1 February 20211 Feb 20211 Feb 2101 Feb 2101 Feb 19211 February 19211 Feb 19211 FEBRUARY 19211 FEB 211 feb 211 february 20211/FEB/2101-02-202101/02/211-FEB 20211/February/211/feb 211-feb/20211.02.211-2-211:2:19211 2-19211/feb-211st of feb 20213rd February 213rd of february 214th Feb 19212ND Feb 211sT feb 21 Example with a flexible month day yearmask (common US style ) Example with a flexible day month yearmask (common UK style ) This workflow demonstrates handling of avariety of dates in Month Day Year order:February 1, 2021Feb 1 2021Feb 1,21Feb 01 21Feb 01, 1921February 01 1921Feb 1st 1921FEBRUARY 1 1921FEB 1 21feb 1,21february 1, 1921FEB/1/2102-01-202102-01-21FEB-1 2021February/1/1921feb/1 21feb-1/192102.1.212-1-212:1:19212-1-1921feb/1-21february 3RD 2021Feb 4th, 1920Feb 2nd-21 Scroll down for different date mask orderexample.... Wide variety of date formatsin column"sample_date"convert date using a flexible date maskd['st']['nd']['rd']['th'] [MMMM][MMM][M] [yyyy][yy]Capitalize Dateusingcapitalize(regexReplace($sample_date$,"[-/\\:.]",""))which converts any of /\:.- into a space and then makes the string Capitalized e.g. 01.feb.2021 becomes 01 Feb 2021The regex was required because capitalize requires white-space around the word(or use Multi Column version if more than one date column)Wide variety of date formatsin column"sample_date"Capitalize Dateusingcapitalize(regexReplace($sample_date$,"[-/\\:.]",""))which converts any of /\:.- into a space and then makes the string Capitalized e.g. 01.feb.2021 becomes 01 Feb 2021The regex was required because capitalize requires white-space around the word(or use Multi Column version if more than one date column)convert date using a flexible date mask[MMMM][MMM][M] d['st']['nd']['rd']['th'] [yyyy][yy] Table Creator String to Date&Time String Manipulation Table Creator String Manipulation String to Date&Time Workflow to demonstrate how to create a very flexible date format mask to handle a wide variety of date formats. This works providedthat the supplied day month and year are are a known order.What I wanted was a date handling mask that, given a specific order of day month year, didn't actually care too much about how thecomponent parts of the date were actually written. The format mask can only go so far though, and some string manipulation isrequired to handle the month name being in anything other than Title (Capitalized) case. So if it comes through as lowercase oruppercase it would fail. Likewise, noise punctuation / \ , . -, whatever needs to be turned to white space and duplicated/superfluouswhite space has to be removed in order for Capitalize to work in all the handled situations.Being in the UK I concentrated first on regular UK ordering D M Y. But scroll down and I have done the same with (my understandingof) typical US ordering as M D YIf you have a locale where they are typically in a different order to that given here, change the order in which d M and y entries appearin the date mask.15/04/2021 @takbb Brian Bates This workflow will correctly handle all of thefollowing (typical UK) date formats:i.e. provided that it appears as day month yearf1 February 20211 Feb 20211 Feb 2101 Feb 2101 Feb 19211 February 19211 Feb 19211 FEBRUARY 19211 FEB 211 feb 211 february 20211/FEB/2101-02-202101/02/211-FEB 20211/February/211/feb 211-feb/20211.02.211-2-211:2:19211 2-19211/feb-211st of feb 20213rd February 213rd of february 214th Feb 19212ND Feb 211sT feb 21 Example with a flexible month day yearmask (common US style ) Example with a flexible day month yearmask (common UK style ) This workflow demonstrates handling of avariety of dates in Month Day Year order:February 1, 2021Feb 1 2021Feb 1,21Feb 01 21Feb 01, 1921February 01 1921Feb 1st 1921FEBRUARY 1 1921FEB 1 21feb 1,21february 1, 1921FEB/1/2102-01-202102-01-21FEB-1 2021February/1/1921feb/1 21feb-1/192102.1.212-1-212:1:19212-1-1921feb/1-21february 3RD 2021Feb 4th, 1920Feb 2nd-21 Scroll down for different date mask orderexample.... Wide variety of date formatsin column"sample_date"convert date using a flexible date maskd['st']['nd']['rd']['th'] [MMMM][MMM][M] [yyyy][yy]Capitalize Dateusingcapitalize(regexReplace($sample_date$,"[-/\\:.]",""))which converts any of /\:.- into a space and then makes the string Capitalized e.g. 01.feb.2021 becomes 01 Feb 2021The regex was required because capitalize requires white-space around the word(or use Multi Column version if more than one date column)Wide variety of date formatsin column"sample_date"Capitalize Dateusingcapitalize(regexReplace($sample_date$,"[-/\\:.]",""))which converts any of /\:.- into a space and then makes the string Capitalized e.g. 01.feb.2021 becomes 01 Feb 2021The regex was required because capitalize requires white-space around the word(or use Multi Column version if more than one date column)convert date using a flexible date mask[MMMM][MMM][M] d['st']['nd']['rd']['th'] [yyyy][yy] Table Creator String to Date&Time String Manipulation Table Creator String Manipulation String to Date&Time

Nodes

Extensions

Links

Download

To use this workflow in KNIME, download it from the below URL and open it in KNIME:

Download Workflow
Created by: brian
Created at: 2021-04-15
On NodePit since: 2021-04-15
Last update: 2024-05-03
Created with KNIME version: v4.3.2
Tags: String to Date&TimeDateString to DateDate conversionParse Date

Deploy, schedule, execute, and monitor your KNIME workflows locally, in the cloud or on-premises – with our brand new NodePit Runner.

Try NodePit Runner!