Skip to main content

DateTime Tool

Use DateTime to transform date-time data to and from a variety of formats, including both expression-friendly and human-readable formats.

Designer Cloud can't process dates prior to January 1, 1400.

Tip

This tool has a One Tool Example. Visit Access Sample Workflows to learn how to access this and many other examples directly in Designer Cloud.

Configure the Tool

Connect a data source to the input anchor of the DateTime tool. In the configuration window, select the format to convert. There are 2 options:

  1. To convert a date-time format to a string...

    • Select the String or Date Column to Convert dropdown to choose a date-time column.

    • Select the Incoming Format for your new column from the list, or select Custom to create your own format. Refer to the Custom Format section below.

    • Enter a New Column Header for the converted data, or use the default DateTime_Out.

  2. To convert string data to a DateTime format...

    • Select the String or Date Column to Convert dropdown and choose a string column.

    • Select the Incoming Format of the incoming string from the list, or select Custom to identify a different format. Refer to the Custom Format section below.

    • Enter a New Column Header for the converted data, or use the default DateTime_Out.

Custom Format

You can specify a custom format that matches either your incoming string field or the new string field that is created.

  1. Select Custom to enter your own format. Designer displays an example based on the format you enter.

  2. Use the specifiers and separators described below (for example, dd, MM, yyyy), or the specifiers and separators supported by the DateTimeParse and DateTimeFormat functions (for example, %d, %m, %Y).

You can use an asterisk (*) as a wildcard character to replace any character in the date format field.

Day, Month, and Year Formats

Format

Explanation

d

Day of the month as digits, without leading 0s for single-digit days.

day

The full name of the day of the week.

dd

Day in 2 digits, with leading 0s for single-digit days. On input, leading 0s are optional.

dy

Day of the week as a 3-letter abbreviation. On input, full names are accepted, but Designer doesn't check that the day of the week agrees with the rest of the date.

EEEE

The full name of the day of the week.

M

A single-digit month, without a leading 0.

MM

Month as digits, with leading 0s for single-digit months. On input, leading 0s are optional.

MMM

The abbreviated name of the month.

MMMM

The name of the month spelled out.

Mon

A 3-letter abbreviation of the name of the month. On input, full names are also accepted.

Month

Name of the Month. On input, abbreviations are also accepted.

yy

Year represented only by the last 2 digits. When converting from a string, 2-digit years are mapped into the range from the current year, minus 66 years to the current year, plus 33 years. For example, in 2016, a 2-digit year will be mapped into the range: 1950 to 2049. On input, 4 digits are also be accepted.

yyyy

Year represented by the full 4 digits. On input, 2 digits will also be accepted and mapped as done for the yy pattern.

Hour, Minute, and Second Format Specifiers

Format

Explanation

H

Hour, with no leading 0s for single-digit hours (24-hour clock).

HH or hh

Hours, with leading 0s for single-digit hours (24-hour clock).

mm

Minutes, with leading 0s for single-digit minutes.

ss

Seconds, with leading 0s for single-digit seconds.

Separators

On output, separators in the date-time format are used exactly. On input...

  • - and/ are accepted as equivalent.

  • White space is ignored.

  • : and , must match exactly.

In this section: