A safe way to convert string to date time in SQL

Over the years while working with SQL Server, I found a safe way of converting string to date time value to be used in SQL. It will have zero risk of being parsed in WHERE clause or cause any performance issues.

Examples:

Convert to DATE type value

SELECT {d '2021-03-01'}

Convert to DATETIME type value

SELECT {ts '2021-03-01 06:10:00'}

Things to know about TRY...CATCH in T-SQL

With TRY…CATCH statement in T-SQL, I attempted to use it for a table name resolution the other day. However, I found the statement will not handle following errors, so it will not work if we are using this statement for checking the table name if it exists or not.

1. Compile errors, such as syntax errors, that prevent a batch from running.
2. Errors that occur during statement-level recompilation, such as object name resolution errors that occur after compilation because of deferred name resolution.

Meanwhile, when using SET XACT_ABORT ON setting, if a T-SQL statement raises a run-time error, the entire transaction is terminated and rolled back. However, this will not help if we want to resolve the “table name does not exist” error.

Reference:

http://stackoverflow.com/questions/15015051/sql-try-catch-doesnt-catch-an-error-when-attempting-to-access-a-table-that-it

COALESCE() vs. ISNULL()

Having been reading the differences between COALESCE() and ISNULL() functions in T-SQL. I have found many other posts talking about this in details and thought put a summary table here for quick reference check.

	COALESCE()	ISNULL()
Parameters (Syntax)	Accept multiple parameters COALESCE ( expression [ ,...n ] ) Example: SELECT COALESCE(NULL, NULL, 'foo')	Accept only two parameters ISNULL( check_expression , replacement_value ) Example: SELECT ISNULL(NULL, ISNULL(NULL, 'foo'))
Data Type Returned	Returns the data type of expression with the highest data type precedence. If all expressions are nonnullable, the result is typed as nonnullable.	Returns the same type as check_expression. If a literal NULL is provided as check_expression, returns the datatype of the replacement_value. If a literal NULL is provided as check_expression and no replacement_value is provided, returns an int.
Column Selected Into	Nullable column returned	Non-nullable column returned
SQL Function Type	Standard function (recommended to use)	Non-standard function
Performance Impact	Not recommended to use in the conditions of joins In some cases where subquery is used in the expression can generate less efficient plan. E.g., SELECT COALESCE (     (SELECT a2.au_id         FROM pubs..authors a2         WHERE a2.au_id = a1.au_id),     '' ) FROM authors a1 See details at http://databases.aspfaq.com/database/coalesce-vs-isnull-sql.html	Not recommended to use in the conditions of joins

References

COALESCE()
http://msdn.microsoft.com/en-us/library/ms190349.aspx

ISNULL()
http://msdn.microsoft.com/en-us/library/ms184325.aspx

Should I use COALESCE() or ISNULL()?
http://databases.aspfaq.com/database/coalesce-vs-isnull-sql.html

Can I have optional parameters to my stored procedures?
http://sqlserver2000.databases.aspfaq.com/can-i-have-optional-parameters-to-my-stored-procedures.html