メインコンテンツまでスキップ
に最終更新

try_parse_timestamp function

Applies to: check marked yes Databricks Runtime 18.1 and above

If expr is a string, parses it into a TIMESTAMP according to the first matching pattern in the given list of formats, or returns NULL if no pattern matches. If expr is a numeric type, parses it as a Unix timestamp. Invalid or non-matching expr returns NULL instead of raising an error.

Syntax​

try_parse_timestamp(expr [, { pattern [...] } ])

Arguments​

  • expr: A string or numeric value to parse into a TIMESTAMP. Accepted types are:
    • TINYINT, SMALLINT, INT, BIGINT
    • FLOAT, DOUBLE
    • DECIMAL (up to 18 precision)
    • STRING
  • pattern: An optional list of timestamp patterns or a reference to a predefined list such as #iso8601 or #rfc3339. The timestamp patterns must be a constant string. If no pattern is specified, it is equivalent to specifying #extended. For the pattern symbol reference and list of predefined pattern lists, see Pattern reference in parse_timestamp function.

Returns​

A TIMESTAMP, or NULL if the expr cannot be parsed.

If the expr is a string and none of the patterns match, the function returns NULL.

If the expr is numeric and cannot be interpreted as a valid Unix timestamp (for example overflow), the function returns NULL.

If the format parameter is malformed (invalid pattern or unknown pattern list), the function still raises an error; only parse failures on the expr value return NULL.

If expr is NULL, the function returns NULL.

Notes​

  • If the pattern list contains NULL values, they are ignored. If all provided patterns are NULL, the result is NULL.
  • If the timestamp matches a pattern containing no timezone information, it is parsed in the session timezone.

Error conditions​

Examples​

SQL
-- Parse timestamp according to default pattern list
> SELECT try_parse_timestamp('2024-12-09T19:30:01');
 2024-12-09 19:30:01.000

-- Invalid date returns NULL
> SELECT try_parse_timestamp('2016-12-32');
 NULL

-- Bad input returns NULL instead of error
> SELECT try_parse_timestamp('bad input');
 NULL

-- Invalid pattern still raises an error (does not return NULL)
> SELECT try_parse_timestamp('2023', 'QQQQ');
 Error: INVALID_PARAMETER_VALUE.PATTERN

-- Extend the default pattern list with custom patterns
> SELECT try_parse_timestamp(col, "#extended", "dd MMM, yyyy", "MMM d; yy") FROM VALUES ('2024-12-01'), ('02 Dec, 2024'), ('DEC 3; 24') AS t(col)
 2024-12-01 00:00:00.000
 2024-12-02 00:00:00.000
 2024-12-03 00:00:00.000