...
Earliest: January 1, 1400
Info 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
| Info |
|---|
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 | ||
| 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)
| Number | 2 | ||
| hh | Hour in am/pm (01-12)
| 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 |
| Info |
|---|
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 |
|---|
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.
Two-
...
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
...
date 01/01/41
...
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 | |
|---|---|
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 20XX year:
| D trans | ||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
|
and
| D trans | ||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
|
Supported Datetime Formats
...
| Code Block |
|---|
"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 | "mm*dd*yyyy" | "shortMonth*dd*yy" |
