April 20, 2016 at 11:07 pm
Comments posted to this topic are about the item More TRY_CONVERTing numbers
April 20, 2016 at 11:09 pm
This was removed by the editor as SPAM
April 21, 2016 at 12:19 am
Good question, as this behavior is expected, same for NCHAR/CHAR as well.
-- For NCHAR
SELECT TRY_CONVERT(NCHAR(1), 234523)
Result :
----
NULL
(1 row(s) affected)
-- For CHAR
SELECT TRY_CONVERT(CHAR(1), 234523)
Result :
----
*
(1 row(s) affected)
Thanks for sharing Steve.
April 21, 2016 at 2:17 am
Although the documentation states that conversion of an int value that is too long will give an error if converting to nvarchar, but * if converting to varchar, it does not say why there is a difference. Does anyone know?
April 21, 2016 at 2:25 am
I think I need to read up on this subject...
April 21, 2016 at 2:43 am
From the explanation:
TRY_CONVERT should obey the CAST and CONVERT rules, which note that truncated values are returned as an E for char and varchar. However, a NULL was returned on multiple machines.
The description for CAST and CONVERT includes a table that specifies the expected results in various "won't fit" cases, showing either * or E depending on input and output data type. However, this is not a literal representation of the expected output. The footnote below the table clarifies that E means that an error is returned because the result length is too short.
And because CONVERT should return an error in this case, TRY_CONVERT should return NULL. The result is not as surprising as the quote above suggests.
April 21, 2016 at 2:45 am
Toreador (4/21/2016)
Although the documentation states that conversion of an int value that is too long will give an error if converting to nvarchar, but * if converting to varchar, it does not say why there is a difference. Does anyone know?
The bane of backwards compatibility. Old versions of SQL Server (and this dates back to the Sybase times) returned * when the result would not fit. An error would have been more logical, so when new data types were introduced they were given that "better" behaviour. Making the same change for already existing data types was not done because that might invalidate existing logic. The SQL Server team usually takes backwards compatibility very seriously.
April 21, 2016 at 4:46 am
Hugo Kornelis (4/21/2016)
From the explanation:TRY_CONVERT should obey the CAST and CONVERT rules, which note that truncated values are returned as an E for char and varchar. However, a NULL was returned on multiple machines.
The description for CAST and CONVERT includes a table that specifies the expected results in various "won't fit" cases, showing either * or E depending on input and output data type. However, this is not a literal representation of the expected output. The footnote below the table clarifies that E means that an error is returned because the result length is too short.
And because CONVERT should return an error in this case, TRY_CONVERT should return NULL. The result is not as surprising as the quote above suggests.
Quite apart from not taking account of the footnote, that part of the explanation is totally wrong. It is clearly documented (on the cast and convert page referenced) that for varchar and char truncation in convert leads not to E but to *. And the suggestion that try_convert returns NULL for truncation with char and varchar is not borne out on either of the machines I have SQLServer 2012 on, so maybe there is a mistype (omitted the initial "n" on the two typenames)? So it seems to me that everything works as documented (even the backward compatibility nastiness), and as you say, none of it is surprising (not even the backwards compatability bringing different results from char and nchar).
Tom
April 21, 2016 at 6:38 am
When I run that select statement in SQL Server 2008 (10.0.5538) I get the error:
SELECT TRY_CONVERT(NVARCHAR(1), 234523)
Msg 195, Level 15, State 10, Line 1
'NVARCHAR' is not a recognized built-in function name.
Is there something in my DB settings that would generate this? I'm pretty sure they're using the install defaults.
Or did I miss some part of the statement when I copied it?
April 21, 2016 at 6:53 am
roger.plowman (4/21/2016)
When I run that select statement in SQL Server 2008 (10.0.5538) I get the error:SELECT TRY_CONVERT(NVARCHAR(1), 234523)
Msg 195, Level 15, State 10, Line 1
'NVARCHAR' is not a recognized built-in function name.
Is there something in my DB settings that would generate this? I'm pretty sure they're using the install defaults.
Or did I miss some part of the statement when I copied it?
TRY_CONVERT is 2012+.
Cheers!
April 21, 2016 at 6:57 am
TomThomson (4/21/2016)
Hugo Kornelis (4/21/2016)
From the explanation:TRY_CONVERT should obey the CAST and CONVERT rules, which note that truncated values are returned as an E for char and varchar. However, a NULL was returned on multiple machines.
The description for CAST and CONVERT includes a table that specifies the expected results in various "won't fit" cases, showing either * or E depending on input and output data type. However, this is not a literal representation of the expected output. The footnote below the table clarifies that E means that an error is returned because the result length is too short.
And because CONVERT should return an error in this case, TRY_CONVERT should return NULL. The result is not as surprising as the quote above suggests.
Quite apart from not taking account of the footnote, that part of the explanation is totally wrong. It is clearly documented (on the cast and convert page referenced) that for varchar and char truncation in convert leads not to E but to *. And the suggestion that try_convert returns NULL for truncation with char and varchar is not borne out on either of the machines I have SQLServer 2012 on, so maybe there is a mistype (omitted the initial "n" on the two typenames)? So it seems to me that everything works as documented (even the backward compatibility nastiness), and as you say, none of it is surprising (not even the backwards compatability bringing different results from char and nchar).
Agreed. Just missing an 'n' on the two data types combined with assuming that the documentation meant a literal 'E' makes the most sense.
That particular bit of documentation came up fairly recently in another QotD for being a bit odd. For NCHAR and NVARCHAR, the table says 'E', and the legend explains that 'E' means an error will be returned. However, for CHAR and VARCHAR, the table says '*', and that is the literal value returned, not what '*' stands for according to the legend.
Not surprisingly, MS could have documented it a bit more clearly 🙂
Cheers!
April 21, 2016 at 7:17 am
Using SQL 2012
-- Msg 195, Level 15, State 10, Line 5
-- 'NVARCHAR' is not a recognized built-in function name.
-- The TRY_Convert function requires 3 argument(s).
SELECT TRY_CONVERT(NVARCHAR(1), 234523)
Did I miss something?
April 21, 2016 at 7:20 am
Pher (4/21/2016)
Using SQL 2012-- Msg 195, Level 15, State 10, Line 5
-- 'NVARCHAR' is not a recognized built-in function name.
-- The TRY_Convert function requires 3 argument(s).
SELECT TRY_CONVERT(NVARCHAR(1), 234523)
Did I miss something?
EDIT: I knee-jerk responded about compatibility level, but I don't think that bit about 3 arguments would come up, just the '...not a recognized built-in function name'.
I'd still check the compatibility level to make sure it's 110, but something else odd is going on with that one.
Cheers!
April 21, 2016 at 9:46 am
It looks that Steve hit the nerve with this one. 🙂
April 21, 2016 at 10:16 am
I said 2. What is wrong with me.
-- Itzik Ben-Gan 2001
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply