Try_Convert Fails on SQL Server 2012

TRY_CONVERT fails on SQL Server 2012

Check that the database compatibility level for the particular database you are using the function in is set to SQL Server 2012 (110) under properties/options/compatibility level for the database.

Try_convert issue * sql server 2012

It is because the resulting datatype is too short to display. As per msdn

Truncating and Rounding Results

When you convert character or binary expressions (char, nchar,
nvarchar, varchar, binary, or varbinary) to an expression of a
different data type, data can be truncated, only partially displayed,
or an error is returned because the result is too short to display.
Conversions to char, varchar, nchar, nvarchar, binary, and varbinary
are truncated, except for the conversions shown in the following
table.

This happens when you convert from int , smallint, or tinyint to char /varchar

TRY CAST is available and TRY CONVERT is not in SQL Server 2012?

Check here, https://learn.microsoft.com/en-us/sql/t-sql/functions/try-cast-transact-sql?view=sql-server-2017 It says TRY_CAST is not a new reserved keyword and is available in all compatibility levels

Which means, TRY_CAST will work for all, but TRY_CONVERT (https://learn.microsoft.com/en-us/sql/t-sql/functions/try-convert-transact-sql?view=sql-server-2017) will work only for 110.

ALTER DATABASE database_name SET COMPATIBILITY_LEVEL = 110

This will surely solve this!

What's wrong with this try_convert usage?

You're getting the error because of the CAST statement inside the try_convert - try_convert won't consume all the errors you generate, it will just return NULL if your convert fails.

If you use try_convert for both attempts it will work:

when try_convert(datetimeoffset, (try_convert(datetimeoffset, substring(@DateString,1,10) + ' ' + substring(@DateString,12,8) + ' ' + substring(@DateString,20,6)))) IS NULL

Working Fiddle of your code here.

Change ISNumeric to try_convert in case statement

You would use:

coalesce(try_convert(float, somecolumn_value), 0.0)

I don't know if the performance will really be better, but the code is easier to read and less susceptible to unexpected problems. Converting to float isn't generally troublesome, but you do have anomalies. For instance, '-' "is numeric" but generates an error when converting to float.

SQL Server Management Studio error converting a string that does not exist

You could use TRY_CAST to get NULL for invalid input data - instead of an exception. ISNUMERIC is notoriously bad at telling whether a column value is really numeric - or not.....

SELECT 
    NumericData
FROM
    (SELECT DISTINCT 
         Data,
         NumericData = TRY_CAST(data AS float) 
     FROM 
         [dbo].[log] 
     WHERE 
         [Time]>='2021-08-04 00:00:00.000' 
         AND Data NOT LIKE '%[^(0-9\-\.)]%') AS t
WHERE 
    t.NumericData > 50

and you could find the offending data by using:

SELECT 
    data,
    NumericData = TRY_CAST(data AS float) 
FROM
    (SELECT DISTINCT Data 
     FROM [dbo].[log] 
     WHERE [Time]>='2021-08-04 00:00:00.000' 
       AND Data NOT LIKE '%[^(0-9\-\.)]%') AS t
WHERE 
    TRY_CAST(data AS float) IS NULL

---

Related Topics