TRY_CONVERT is one of the new conversion functions shipped with SQL Server. This function converts expression from one data type to another data type. The beauty of this function is that if it fails to convert, it returns NULL value as a results and due to this functionality, it has an edge over CONVERT function. I started using this function wherever conversion required since SQL Server 2012, however, I received an error message this morning when I was trying to use TRY_CONVERT function in SQL Server 2014 as shown below.
Let me explain this error in detail :
Message Number: 195
Severity : 15
Error Message: ‘TRY_Convert’ is not a recognized built-in function name.
Let me create a sample using TRY_Convert function to demonstrate this error.
USE SampleDB GO SELECT TRY_CONVERT(datetime2, '12/31/2010') AS Result; GO SELECT @@VERSION GO
Msg 195, Level 15, State 10, Line 5
‘TRY_CONVERT’ is not a recognized built-in function name.
Microsoft SQL Server 2014 – 12.0.2000.8 (X64)
Ooopps…… I am unable to use TRY_CONVERT even though I am using SQL Server 2014 as shown above.
The resolution is very simple, actually, what you are trying to do is to use TRY_CONVERT function in one of the databases having compatibility less than 110 even though you are using SQL Server 2014. Lets fix this issue step by step.
Step 1: Check compatibility
USE SampleDB; GO SELECT compatibility_level FROM sys.databases WHERE name = 'SampleDB'; GO --OUTPUT
(1 row(s) affected)
Step 2: Change compatibility
As you can see above the compatibility of the database is below 110, lets change it to enjoy the core functionality of SQL Server 2014.
Note : Test the compatibility change of your database on your test/development database first, before proceeding to production database.
USE master GO ALTER DATABASE SampleDB SET COMPATIBILITY_LEVEL = 120; GO
Step 3: Use TRY_CONVERT
USE SampleDB GO SELECT TRY_CONVERT(datetime2, '12/31/2010') AS Result; GO --OUTPUT
Remember, whenever you use NEW SQL Server functions that are compatible with specific versions / compatibility level, you MUST check the version/ compatibility before implementing those functions.