Skip to main content

DateTime Tool

Use DateTime to transform date-time data to and from various formats, including 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 to see the columns available to convert.

  1. Select a Column to Convert. You can only convert date-time data types to a string or string data types to a date-time format.

  2. 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.

  3. Enter a New Column Name 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. Use Custom Format to enter your own format, or select the plus icon to enter a preset date-time format.

  2. Use the specifiers and separators described below (for example, dd, MM, yyyy).

Day, Month, and Year Formats

Format

Explanation

day

The full name of the day of the week.

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.

%u

Day in 1 digit (1-7).

dd

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

MM

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

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

%I

Hours, with leading 0s for single digit hours on a 12-hour clock (00-12).

hh

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

%p

Add AM or PM.

%X

Hour, minute, second with AM and PM.

mm

Minutes, with leading 0s for single-digit minutes (00-59).

ss

Seconds, with leading 0s for single-digit seconds (01-59).

%L

3-digit millisecond (000-999).

%j

3-digit day of the year.

%W

2-digit week number (00-53).

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: