CAST with NUMERIC(12,2) using INT variables
-
Hi,
DECLARE @p1 AS INT = 9, @p2 AS INT = 2;
SELECT CAST(@p1 AS NUMERIC(12, 2) / CAST(@p2 AS NUMERIC(12, 2));
Using the above I get '4.500000000000000'.Surely it should be '0000000004.50'?
I am using SQL Server Management Studio v17.9.1 for this.
-
The result is correct, use the str function, like:
select str(cast(@p1 as numeric(12, 2)) / cast(@p2 as numeric(12, 2)), 12, 2); - jonas.gunnarsson 52434 - Thursday, March 7, 2019 11:54 PM
No need to convert the number to a string, unnecessary overhead.
😎Here is an example wrapping the calculation in a convert function.
DECLARE @p1 AS INT = 9, @p2 AS INT = 2;
SELECT
CONVERT(NUMERIC(12,2),CAST(@p1 AS NUMERIC(12, 2)) / CAST(@p2 AS NUMERIC(12, 2)),0); -
USE Test
GO
DECLARE @p1 AS INT = 9, @p2 AS INT = 2;
SELECT CAST(@p1 AS NUMERIC(12, 2))/ CAST(@p2 AS NUMERIC(12, 2)) ReturnResult
INTO dbo.TestDivisionReturnType;
GO
EXEC sp_help 'dbo.TestDivisionReturnType'
GO
DROP TABLE dbo.TestDivisionReturnType
The return type of numeric(12,2) / numeric(12,2) is numeric(27,15) -
the result is correct. by convention whole numbers(integers) are not padded with leading zeros, decimals are however.
- Clocker - Friday, March 8, 2019 4:17 PM
Where on Earth did you come up with such a convention?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Jeff Moden - Sunday, March 10, 2019 3:29 PM
And show me even one product that will actually left-pad a number with leading 0's by default.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) - sgmunson - Monday, March 11, 2019 11:01 AM
My guess is BOL 😀
😎
This is just like dealing with managers, their importance is normally equivalent to the importance of leading and trailing zeros😉 - Eirikur Eiriksson - Monday, March 11, 2019 11:30 AM
Correct me if I'm wrong, Eirikur, but you make it sound like it states such a convention in BOL. To save some time, do you have link?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Jeff Moden - Monday, March 11, 2019 1:13 PM
Read BOL =
Bullshit On Line
😎
l - Eirikur Eiriksson - Monday, March 11, 2019 2:16 PM
OH!!! THAT BOL!!! 😀
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thanks for the responses guys....
- Jeff Moden - Monday, March 11, 2019 2:21 PM
+ a googolplex to the googolplex power, cubed !!!
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) - sgmunson - Wednesday, March 13, 2019 7:31 AM
Spot on Steve 😉
😎 - Eirikur Eiriksson - Monday, March 11, 2019 2:16 PM
SOM (for those who don't remember: SOM = spewing on monitor ) 😛
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply