AsDate

Description

Converts a character sequence into a date value using the specified date format and returns the date value in a new column.

The string field value containing the date must include time characters.

  • AsDate cannot convert "24/05/2021" into a date data type.
    • Format dd/MM/yyyy is not supported.
  • AsDate can convert "24/05/2021 00:00:00" into a data type.
    • Format dd/MM/yyyy HH:mm:ss is supported.

Use

  • Select an argument. (String)
  • Enter the date format pattern or patterns separated by new line.
    • The top date format that can parse the date parses the date (input by input)
    • E.g., yyyy-MM-dd HH:mm:ss
  • Select the source time zone. The source time zone is important to interpret the input dates when time zone information is absent.
    • E.g., 2020-12-03 12:22:52.284 can mean different points of time in UTC time, according to Europe/Berlin time zone or New York time zone
    • E.g., 2020-12-03 12:22:52.284+1:00 means only one point of time in UTC time.
  • Locale is important if you want to parse Month by names. It describes the input language.
  • Fail on unparsable input (and nulls):
    • If marked, the Flow fails on an unparsable input.
    • If unmarked, AsDate produces null as output for unparsable input.
    • For null and empty input, AsDate always returns null (regardless the box is checked or unchecked)
  • Enter a name for the output field.
  • Click OK.

Format

Format is a flexible way to describe how to parse the input strings. It gives the freedom to handle almost every input string. 

Sample formats

NameFormatSampleDescription
ISO Local Date and Time
yyyy-MM-dd'T'HH:mm:ss
2011-12-03T10:15:30ISO compatible date time format without time zone info.
Date Time with Offsetyyyy-MM-dd'T'HH:mm:ssXXX2011-12-03T11:15:30+01:00
2011-12-03T10:15:30Z
ISO compatible date time format with time zone info.
German format

dd.MM.yyyy HH:mm:ss

03.12.2011 10:15:30
US formatMM/dd/yyyy HH:mm:ss12/03/2011 10:15:30

US format

(Month as abbreviation)

MMM/dd/yyyy HH:mm:ssDec/03/2011 10:15:30
UK formatdd/MM/yyyy HH:mm:ss03/12/2011 10:15:30
UK format
(Month as text)
dd/MMMM/yyyy HH:mm:ss03/December/2011 10:15:30

Craft your own format

Put the character blocks after each other, with separators.

Good to know:

  • Lower and uppercase characters are different. Date formatting is case sensitive.
  • Add:
    • exactly one character block for year, month, day, hour, minute.
    • one block for seconds, fractional seconds, and the time zone info.
CategorySubcategoryFormatSample input
Year

2 digit

(result year will be within the range 2000 to 2099 inclusive)

yy

12

01


4 digityyyy

2012

1973

Month1-2 digitM

1

12


2 digits (strict, zero-padding expected)MM

01

12


Abbreviation (localized)MMM

Dec

Jan


Full name (localized)MMMM

December

January

Day1-2 digitd

1

12


2 digits (strict, zero-padding expected)dd

01

12

Separators

non-letter characters can be put between categories,

except: ' # {} [] 

write the expected character

/

-

.

:

space


letter-based separator can be put between categories, 

between an opening and closing '

'T'

'at'

T

at 

Hour (0-23)1-2 digitH

1

12


2 digits (strict, zero-padding expected)HH

01

12

Hour (1-12)1-2 digith

1

10


2 digits (strict, zero-padding expected)hh

01

12


am-pm-of-day
(input should be uppercase for almost every locale)
a
PM
AM
Minute1-2 digitm

1

35


2 digits (strict, zero-padding expected)mm

01

35

Seconds1-2 digits

1

35


2 digits (strict, zero-padding expected)ss

01

35

Fractional seconds3 digitsSSS934
Time zoneZone offset (Z or plus minus hour)X

Z

+01

-08


Zone offset (Z or plus minus hour minute without colon)XX

Z

+0130

-0800


Zone offset (Z or plus minus hour minute with colon)XXX

Z

+01:30

-08:00


Zone name (short)z
UTC
PST

Zone name (full)zzzzPacific Standard Time

The full documentation is at  https://docs.oracle.com/javase/8/docs/api/java/time/format/DateTimeFormatter.html 

Example

The input dates is:  6/15/2009 1:45 PM

Crafting the format:

  • first is month, single digit form is allowed: M
  • a slash: /
  • the day is currently two digit long, it is better to use a single because the month is not padded. It is expected the day will also not be padded.
  • a slash: /
  • year: yyyy
  • a space: 
  • hour is with am/pm mode and single digit is allowed, h
  • a colon: :
  • minutes: m
  • a space:  
  • an 'a' for reading AM/PM: a
  • complete, no seconds or time zone in this example.

Let's put it together: M/d/yyyy h:m a


Type

Formulas