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
, 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
D s product
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: 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