SAFE_TO_TIMESTAMP

Return a TIMESTAMP value from a VARCHAR string that represents a timestamp.

SAFE_TO_TIMESTAMP(input_timestamp 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 YYYYMMDDHH24MISS. If you specify any other format, the function returns NULL.
Each part of the timestamp must be present in the input string, except for seconds, which may be omitted. The function assumes 0 seconds if seconds are omitted. If any other date part is missing, the function returns NULL.
The input string cannot contain delimiter characters between the date and time 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.
The ranges of supported hours, minutes, and seconds are as follows. If values fall outside these ranges, the function returns NULL:
Hours: 0-23
Minutes: 0-59
Seconds: 0-59

For example:

premdb=# select safe_to_timestamp('19991231235959', 'YYYYMMDDHH24MISS');
  safe_to_timestamp  
---------------------
 1999-12-31 23:59:59
(1 row)

In this example, the input string does not contain any seconds, so the function returns 00 for the seconds:

premdb=# select safe_to_timestamp('202301012359', 'YYYYMMDDHH24MISS');
  safe_to_timestamp  
---------------------
 2023-01-01 23:59:00
(1 row)

The following example returns NULL because the timestamp format is incorrect:

premdb=# select safe_to_timestamp('202301012359', 'YYYYMMDDHH24');
 safe_to_timestamp 
-------------------
 [NULL]
(1 row)

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

premdb=# select safe_to_timestamp('202301012359', 'YYYYMMDDHH24:MI:SS');
 safe_to_timestamp 
-------------------
 [NULL]
(1 row)

The following example returns NULL because the input string contains an invalid hours value (24):

premdb=# select safe_to_timestamp('202301012401', 'YYYYMMDDHH24MISS');
 safe_to_timestamp 
-------------------
 [NULL]
(1 row)

SAFE_TO_TIMESTAMP ​