View Source

supports a variety of Datetime formats, each of which has additional variations to it.

Date Range

Supported Date Ranges:

Earliest: January 1, 1400
Latest: December 31, 2599

You can use dates in the Gregorian calendar system only. Dates in the Julian calendar are not supported.

Formatting Tokens

You can use the following tokens to change the format of a column of dates:

Letter Date or Time Component Presentation Examples

M Month in year Number 1

MM Month in year Number 01

MMMM Month in year Month January

MMM Month in year Month Jan

yy Year Number 16

yyyy Year Number 2016

D Day in year Number 352

d Day in month Number 9

dd Day in a month Number 09

EEE Day in week (three-letter abbreviation) Text Wed

EEEE Day in week Text Wednesday

h

Hour in day (1-12)

NOTE: Requires an AM/PM indicator (a).

Number

2

hh

Hour in am/pm (01-12)

NOTE: Requires an AM/PM indicator (a).

Number

02

H Hour in day (1-12) Number 2

HH Hour in day (0-23) Number 20

m Minute in an hour Number 9

mm

Minute in an hour

Number

09

s Second in a minute Number 3

ss Second in a minute Number 03

SSS Millisecond Number 218

X Time zone ISO 8601 time zone -08:00

a AM/PM indicator String AM

NOTE: When publishing to relational targets, Datetime values are written as date/time values in newly created tables. If you are appending to a relational table column that is in timestamp format, Datetime values can be written as timestamps.

Tip: If your DateTime column contains data in multiple formats, you must change the format of the DateTime column to one format and then add a transformation to convert that data to the other format. When all formats of your source date values are converted to a single format, the application should infer the appropriate date and time format.

Supported Separators:

Date separators: blank space, comma, single hyphen, or forward slash
Time separators: blank space, comma, single hyphen, colon, t or T
Non-delimited Datetime values are supported. For example, yyyymmdd, yyyymmddThhmmssX.

ISO 8601 Time Zone Notes:

Support for timezone offset from UTC indicated by +hh:mm, +hhmm, or +hh. For example, the date '2013-11-18 11:55-04:00' is recognized as a DateTime value.
Datetime part functions (for example, Hour) truncate time zones and return local time.
If you have a column with multiple time zones, you can convert the column to Unixtime so you can perform Date/Time operations with a standardized time zone. If you want to work with local times, you can truncate the time zone or use other Datetime functions. See UNIXTIME Function.

Supported Datetime Formats

For more information on the available formats and examples of each one, see Datetime Formats (PDF).

For more information on supported date formatting strings, see DATEFORMAT Function.

Supported Time Zones

For more information, see Supported Time Zone Values.

Datetime Schema via API

When Datetime data is returned via API calls, the schema for this information is returned as a three-element array. The additional elements to the specific are required to account for formatting options of for Datetime values.

Tip: Schema information for data types is primarily available via API calls. You may find schema information for columns in JSON versions of the visual profile and flow definitions when they are exported.

Example:

"end_date": [
            "Datetime",
            "mm-dd-yy",
            "mm*dd*yyyy"
        ]

Array Element	Description	Example 1	Example 2
Data type	The internal name for the data type. For Datetime columns, this schema value should always be `Datetime`.	`"Datetime"`	`"Datetime"`
Sub-format	The general format category of the data type	`"mm-dd-yy"`	`"mm-dd-yy"`
Format type	The specific formatting for the data type	`"mmddyyyy"`	`"shortMonthddyy"`