String to Date&Time

Parses the strings in the selected columns and converts them into Date&Time cells. The expected format can be selected from a number of commonly used formats or specified manually (see section "Type and Format Selection").

Since dates may contain localized terms such as month or weekday names, you can additionally specify a locale.

Options

Column Selection

Column Selector: Only the included columns will be parsed.

Replace/Append Selection

Append selected columns: The selected columns will be appended to the input table. The suffix of the appended columns can be provided in the text field to the right.
Replace selected columns: The selected columns will be replaced by the converted columns.

Type and Format Selection

New type

The type of the new data cells can be selected.

Date format

A template ("format string") that specifies how to interpret the input. This template may contain placeholders for different descriptions of month names, weekday names, month and day numbers, year numbers etc., as well as other non-alphabetic characters that are expected to appear in the input string.

Commonly used placeholders are y for year, M for month, d for day of month, H for hours, m for minutes and s for seconds. A full list of placeholders can be found below.

The count of an individual placeholder letter defines the expected format. For example, a weekday can be represented by its full name ("Monday"), a short name ("Mon") or a narrow name ("M"). Commonly used expressions are:

yyyy matches "2021". yy matches years relative to the year 2000: "21" will be interpreted as "2021".
MM matches month numbers like "01", MMM matches short month names like "Jan", MMMM matches full month names like "January".
d matches day numbers like "5" and "05".

Examples:

yyyy.MM.dd HH:mm:ss.SSS parses dates like "2001.07.04 12:08:56.000"
yyyy-MM-dd'T'HH:mm:ss.SSSZ parses dates like "2001-07-04T12:08:56.235-0700"
yyyy-MM-dd'T'HH:mm:ss.SSSXXX'['VV']' parses dates like "2001-07-04T12:08:56.235+02:00[Europe/Berlin]"

Placeholders can be declared as optional by wrapping them in square brackets. This is useful if it is unclear whether the input contains a certain field or not. For example:

HH:mm:ss[.SSS] parses times like "12:08:56.123" and "12:08:56"
yyyy-MM-dd'T'HH:mm[:ss[.SSS]] parses dates like "2001-07-04T12:08:56.235", "2001-07-04T12:08:56" and "2001-07-04T12:08"

Optional placeholders can also be useful if different formats are to be expected. For example, [MMMM][MMM] dd, yyyy matches both "April 02, 2021" and "Apr 02, 2021". Note that this will also match when no month description is given whatsoever.

The full list of supported placeholders is given below. You can find a full, technical specification here.

G: era
u: year
y: year of era
D: day of year
M: month in year (context sensitive)
L: month in year (standalone form)
d: day of month
Q: quarter of year
q: quarter of year
Y: week based year
w: week of week based year
W: week of month
E: day of week
e: localized day of week
c: localized day of week
F: week of month
a: am/pm of day
h: clock hour of am/pm (1-12)
K: hour of am/pm (0-11)
k: clock hour of am/pm (1-24)
H: hour of day (0-23)
m: minute of hour
s: second of minute
S: fraction of second
A: milli of day
n: nano of second
N: nano of second
V: time zone ID
z: time zone name
O: localized zone offset
X: zone offset (Z for zero)
x: zone offset
Z: zone offset
p: pad next
': escape for text
'': single quote
[: optional section start
]: optional section end

Locale

A locale can be chosen, which determines the language and geographic region for terms such as months or weekdays.

Content of the first cell

Shows the content of the first non-missing cell of the first included column to simplify format selection.

Guess data type and format

If pressed the data type and the format are guessed based on the content of the first cell. If none of the formats in the list is suitable, type and format will not change.

Abort Execution

Fail on error: If checked, the node will abort the execution and fail on errors. If unchecked, missing values will be generated instead.

Input Ports

: Input table.

Output Ports

: Output table containing the parsed columns.

Popular Predecessors

Popular Successors

Views

This node has no views

Workflows

Developers

You want to see the source code for this node? Click the following button and we’ll use our super-powers to find it for you.

Installation

To use this node in KNIME, install the extension KNIME Base nodes from the below update site following our NodePit Product and Node Installation Guide:

v5.2

A zipped version of the software site can be downloaded here.

Plugin provider: KNIME AG, Zurich, Switzerland

Plugin version: 5.2.0.v202310301554

On NodePit since: 2023-12-06

Last update: 2024-04-25

Tags: Streamable

KNIME versions: Since v3.6

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

Try NodePit Runner!