Datetime Data Type

Date Range

Supported Date Ranges:  

• 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/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. As a workaround, you can use the DATEFORMAT function to reformat these dates as four-digit values (01/01/1941). See DATEFORMAT Function.

• Latest: December 31, 2599

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

Data Validation

When values are validated against the Datetime data type, the Dataprep by Trifacta application does not compare them to an underlying calendar system. Instead, the application validates the values using regular expressions. This regular expression method checks for general Datetime validation and is fast to evaluate.

However, some values may follow the regular expression validation pattern but are not accurate dates. For example, every four years, February 29 is a valid date. When this date is validated against the Datetime data type, it may be detected as a valid value, while the date is changed in the application to be incremented to a close accurate date, such as March 1 in this example.

Formatting Tokens

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

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.

Two-digit year values

Depending on the system, a two-digit value for year in a Datetime value is subject to different interpretations. In  Dataprep by Trifacta, 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/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. 

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: 

and

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.

Job Execution

Datetime data typing involves the basic type definition, plus any supported formatting options. Depending on where the job is executed, there may be variation in how the Datetime data type is interpreted. 

• Some running environments may perform additional inference on the typing.

  NOTE: During job execution on Spark, inputs of Datetime data type may result in row values being inferred for data type individually. For example, the String value 01/10/2020 may be inferred by date transformations as 1st Oct, 2020 or 10th Jan, 2020. Resulting outputs of Datetime values may not be deterministic in this scenario.

• Some formatting options may not be supported.