When should I use SELECT CAST or SELECT SAFE_CAST instead of just CAST in SQL?

Question

Would there be much difference if I used CAST instead of SELECT CAST or SELECT SAFE_CAST? Thank you in advance.

I'm having trouble understanding when to use these. I understood CAST but then I learned SELECT CAST and SELECT SAFE_CAST wondering if there are kinds of scenarios when it's only applicable to use SELECT SAFE_CAST or SELECT CAST.

Answer 1

SAFE_CAST is best used if there is a likelihood of a casting error for example if converting strings to dates the string 31-Feb-2023 cannot be converted to a date. In this case SAFE_CAST returns a NULL and allows the query to proceed. In the final output the user may then inspect all cases where the conversion resulted in NULL and perhaps correct the input to a proper date. Using CAST alone would just produce an error message and you would probably not know which row caused the error. e.g.

CREATE TABLE date_table (
    valid_date date,
    string_date varchar(20)
);

INSERT INTO date_table (valid_date, string_date)
VALUES
    ('2023-01-01', '31-Feb-2023'),
    ('2023-02-15', '14-Mar-2023'),
    ('2023-03-10', 'ABC-2023-04-01');


-- Example using SAFE_CAST function
SELECT valid_date, SAFE_CAST(string_date AS date) AS converted_date
FROM date_table;

However, this will crash because some strings just cannot be converted into a date:

-- Example using CAST function TRY THIS
SELECT valid_date, CAST(string_date AS date) AS converted_date
FROM date_table;

In the opposite direction, if we have a column of a specific data type and wish to convert that to strings then you don't require safe_cast:

SELECT CAST(valid_date AS text) AS string_date
FROM date_table;

although it is more likely you would format e.g.

SELECT valid_date, TO_CHAR(valid_date, 'FMMonth DD, YYYY') AS formatted_date
FROM date_table;

Perhaps try the above SQL at https://dbfiddle.uk