June 14, 2018 at 11:17 pm
Comments posted to this topic are about the item TRY_CONVERT
June 14, 2018 at 11:17 pm
Nice question to end the week on, thanks Steve
____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
June 15, 2018 at 2:24 am
I'm not sure why it doesn't work (although the value won't convert directly using cast), but if you try:-
SELECT CAST(CAST('1.23' AS FLOAT) AS int)
that works.
Is this a flaw in the TRY_CONVERT logic or is it just going for a direct conversion without trying other options (i.e. nested CASTs)?
June 15, 2018 at 3:35 am
richardmgreen1 - Friday, June 15, 2018 2:24 AMI'm not sure why it doesn't work (although the value won't convert directly using cast), but if you try:-
SELECT CAST(CAST('1.23' AS FLOAT) AS int)
that works.
Is this a flaw in the TRY_CONVERT logic or is it just going for a direct conversion without trying other options (i.e. nested CASTs)?
It's going for the direct conversion,. The equivalent of your nested CAST would be
SELECT TRY_CONVERT(int,TRY_CONVERT(float, '1.23'))
which returns 1
June 15, 2018 at 5:17 am
richardmgreen1 - Friday, June 15, 2018 2:24 AMI'm not sure why it doesn't work (although the value won't convert directly using cast), but if you try:-
SELECT CAST(CAST('1.23' AS FLOAT) AS int)
that works.
Is this a flaw in the TRY_CONVERT logic or is it just going for a direct conversion without trying other options (i.e. nested CASTs)?
Interesting tidbits:
You can convert a decimal value to an int
DECLARE @x decimal(5,2)
SET @x = 1.23
SELECT TRY_CONVERT(int,@x)
SELECT CAST(@x as int)
That works.
But you cannot convert a character representation of a decimal to an int
DECLARE @a char(5)
SET @a = '1.23'
SELECT TRY_CONVERT(int,@a)
SELECT CAST(@a as int)
That returns NULL for the first select; the second fails.
You can first cast it to decimal, and then to int
DECLARE @a char(5)
SET @a = '1.23'
SELECT TRY_CONVERT(int,TRY_CONVERT(decimal,@a))
SELECT CAST(CAST(@a as decimal) as int)
I guess that all makes sense. If you have some character value in your db, do you really want a query to cast it to integer, if it's not an integer?
June 15, 2018 at 5:52 pm
gvoshol 73146 - Friday, June 15, 2018 5:17 AM<big snip>
I guess that all makes sense. If you have some character value in your db, do you really want a query to cast it to integer, if it's not an integer?
Yes. For example, you may want to store a model date of a product as a year only string, but later you may want to find how many years ago that happened. Month and day may be unknown or irrelevant, so you convert the year to int and deduct it from the current year as int.
June 18, 2018 at 3:35 am
Cat among the pigeons: If you use TRY_CONVERT on a string, you as developer deliberately did so. So then I would want a value to be returned if possible.
5ilverFox
Consulting DBA / Developer
South Africa
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply