|
I can't rate this one because of a major conflict. You're an outstanding presenter and your demonstrations are clean, simple, and well explained. However... I can't recommend using STR by itself for formatting. You might want to follow this up with some of the caveats of using the STR function. What most people don’t know is that the STR function first converts the number to a FLOAT. From there on, you can suffer all the same problems that you do with the FLOAT data-type…
--===== Typical rounding problems same as FLOAT
WITH
cteGenerateExampleData AS
(
SELECT TOP 1000000
SomeNumber = ROW_NUMBER() OVER(ORDER BY (SELECT NULL))*0.0001
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
)
,
cteConversions AS
(
SELECT SomeNumber,
SomeRound = ROUND(SomeNumber,2),
SomeDecimal = CAST(SomeNumber AS DECIMAL(10,2)),
SomeSTR = STR(SomeNumber,10,2)
FROM cteGenerateExampleData
)
SELECT *,
ISNULL(CASE WHEN SomeDecimal <> SomeRound THEN 'Bad Decimal Rounding ' END,'')
+ ISNULL(CASE WHEN SomeStr <> SomeRound THEN 'Bad STR Rounding ' END,'')
FROM cteConversions
WHERE SomeDecimal <> SomeRound
OR SomeStr <> SomeRound
;
--===== Accuracy only to 15 digits
SELECT 9223372036854775807, STR(9223372036854775807,19) UNION ALL --Incorrect STR value
SELECT 922337203685477580, STR(922337203685477580,19) UNION ALL --Incorrect STR value
SELECT 92233720368547758, STR(92233720368547758,19) UNION ALL --Incorrect STR value
SELECT 9223372036854775, STR(9223372036854775,19) UNION ALL --Incorrect STR value
SELECT 922337203685477, STR(922337203685477,19) --Finally! Correct answer!
--===== Accuracy only to 15 digits
SELECT .9223372036854775807, STR(.9223372036854775807,19,18) UNION ALL --Incorrect STR value
SELECT .922337203685477580, STR(.922337203685477580,19,18) UNION ALL --Incorrect STR value
SELECT .92233720368547758, STR(.92233720368547758,19,18) UNION ALL --Incorrect STR value
SELECT .9223372036854775, STR(.9223372036854775,19,18) UNION ALL --Incorrect STR value
SELECT .922337203685477, STR(.922337203685477,19,18) --Finally! Correct answer!
--Jeff Moden
jbmoden@ameritech.net
|
Dennis Allen on
11/3/2010
You bring up a good point with, contrasting with convert(). I am interested in contrasting convert() and cast(). For myself, I had been using convert() in all cases with the thought of the fewer functions I use, the easier to understand my work. Is there a value to using cast() over convert? I can see a clear value with convert() in the case of date and time, but I have not found (other than number of characters typed) a benefit for cast().
|