Product Documentation

Appearance

All platforms

SAFE_TO_DATE ​

Return a DATE data type from a VARCHAR string that represents a date in YYYYMMDD format.

SAFE_TO_DATE(input_date varchar, format varchar)

The input VARCHAR string is the first argument, and its format is the second argument.

Note the specific requirements of this function:

  • The specified format must be exactly YYYYMMDD. If you specify any other format, the function returns NULL.
  • Each part of the date must be present in the input string (year, month, and day).
  • The input string cannot contain delimiter characters between the date parts, such as - or / characters.
  • The range of supported dates is from 0001-01-01 to 9999-12-31. Dates that fall outside this range cannot be cast and return NULL.

For example:

yellowbrick=# select safe_to_date('19600309','YYYYMMDD');
 safe_to_date 
--------------
 1960-03-09
(1 row)

The following example returns NULL because the date format is incorrect.

yellowbrick_test=# select safe_to_date('1960-03-09','MDY');
 safe_to_date 
--------------
 [NULL]
(1 row)

The following example returns NULL because the input string contains delimiters between the date parts:

yellowbrick_test=# select safe_to_date('1960-03-09','YYYYMMDD');
 safe_to_date 
--------------
 [NULL]
(1 row)

To enable this function, set the enable_safe_compat_functions_with_nz configuration parameter.