CODEWITHSUNDEEP
snowflake-cloud-data-platform
Hugo Tremblay
Hugo Tremblay

Reputation: 31

TRY_TO_DATE with pattern?

Snowflake has a TO_DATE(string, pattern) function, but currently has no TRY_TO_DATE version of the function with the same signature. While we've built a javascript parsing function as a workaround, it has abysmal performance due to its complexity and the fact that it's performance could never measure against a real native function.

Is there a roadmap for the availability of such native function? Is there a community voting board to prioritize such function?

Upvotes: 3

Views: 2013

Answers (2)

Lukasz Szozda
Lukasz Szozda

Reputation: 176324

Release Notes - August 3-6, 2020

TRY_TO_DATE(), TRY_TO_TIME(), and TRY_TO_TIMESTAMP() Functions: Support Added for Optional Format Specifier

With this release, the TRY_TO_DATE(), TRY_TO_TIME(), and the TRY_TO_TIMESTAMP() family of functions support an optional format specifier, similar to the corresponding TO_DATE(), TO_TIME(), and TO_TIMESTAMP() functions.

TRY_TO_DATE

A special version of TO_DATE , DATE that performs the same operation (i.e. converts an input expression to a date), but with error-handling support (i.e. if the conversion cannot be performed, it returns a NULL value instead of raising an error).

Syntax

TRY_TO_DATE( <string_expr> [, <format> ] )

Upvotes: 1

Gokhan Atil
Gokhan Atil

Reputation: 10199

Although there is not a parameter for this function, you can set DATE_INPUT_FORMAT to force a pattern for TRY_TO_DATE function:

ALTER SESSION SET DATE_INPUT_FORMAT = 'YYYY-MM-DD';
SELECT TRY_TO_DATE('2020-04-05'); -- will return a date value

ALTER SESSION SET DATE_INPUT_FORMAT = 'DD-MM-YYYY';
SELECT TRY_TO_DATE('2020-04-05'); -- will return NULL

https://docs.snowflake.com/en/sql-reference/parameters.html#date-input-format

You can use Snowflake Ideas to suggest and vote for new features:

https://community.snowflake.com/s/ideas

Upvotes: 1

Related Questions