Earliest: January 1, 1400
NOTE: Two-digit values for the year that are older than 80 years from the current year are forward-ported into the future. For example, in a job run on Dec 31, 2021, the date
01/01/41is interpreted as 01/01/1941. However, if the job is run the next day (January 01, 2022), then the same data is interpreted as 01/01/2041. See "Two-digit year values" below.
- Latest: December 31, 2599
NOTE: The supported date ranges can be modified if needed. For more information, see Configure Application Limits.
|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|
|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|
Hour in day (1-12)
Hour in am/pm (01-12)
|H||Hour in day (1-12)||Number||2|
|HH||Hour in day (0-23)||Number||20|
|m||Minute in an hour||Number||9|
|Minute in an hour||Number||09|
|s||Second in a minute||Number||3|
|ss||Second in a minute||Number||03|
|X||Time zone||ISO 8601 time zone|
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.
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.
digit year values
Depending on the system, a two-digit value for year in a Datetime value is subject to different interpretations. In
|D s product|
For example, in a
job run on Dec 31, 2021, the
is interpreted as 01/01/1941. However, if the job is run the next day (January 01, 2022), then the same data is interpreted as 01/01/2041.
Other systems use different limits for backward versus forward porting of year values:
- In BigQuery, if no century value is provided, then the year value has a century value applied to it based on a fixed range. See https://cloud.google.com/bigquery/docs/reference/standard-sql/format-elements#:~:text=The%20year%20without%20century%20as%20a%20decimal%20number%20(00%2D99).
- Snowflake permits customization of two-digit year values at the Account, Session, or Object level. See https://docs.snowflake.com/en/sql-reference/parameters.html#two-digit-century-start.
As a result, it can be a challenge to manage these system-dependent two-digit years in a consistent manner.
Tip: For best results, you should format year values as four-digit values before the data is ingested into
If the above is not possible, you can create replacement steps in your recipe to convert two-digit years to four-digit values. In the following example,
00-39 is interpreted as a
19XX year, while
40-99 is interpreted as a
Supported Datetime Formats
"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 |
|Sub-format||The general format category of the data type|
|Format type||The specific formatting for the data type|