June 12, 2009 at 3:25 am
Hi, I have a line of code as follows:
'Qty: ' + CONVERT(nvarchar(10), i.Quantity),
This gives output: 10.00000
I would like output to read: 10.00
I tried to include CAST, my latest offering below
'Qty: ' + CAST(CONVERT(i.Quantity AS nvarchar(10) AS DECIMAL(5,2)),
which errors.
Is it possible to CONVERT then output to 2 decimal places as required.
Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
June 12, 2009 at 3:36 am
Thanks, now that's what I call service 🙂
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
June 12, 2009 at 3:44 am
Is there a way to make this dynamic as the Qty varies.
Full code as follows:
DECLARE @Qtyint
SET @Qty = 10.00
SELECT
p.ProductId,
p.ProductDescription,
i.LotNumber,
--'Qty: ' + CONVERT(nvarchar(112), i.Quantity),
STR(@Qty,5,2) AS Qty,
l.LocationId AS DefaultLocation
FROM GoodsReceipts AS gr
INNER JOIN Inventory AS i ON gr.GoodsReceipt = i.GoodsReceipt
INNER JOIN Products AS p ON i.Product = p.Product
INNER JOIN Locations AS l ON p.Location = l.location
Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
June 12, 2009 at 4:04 am
Hi, no matter what the actual quantity the value retunred is always 10.
I thought I might try the following (where [Identifier] is the value passed by the app)
DECLARE @Qtyint
SET @Qty =
(SELECT i.Quantity
FROM GoodsReceipts AS gr
INNER JOIN Inventory AS i ON gr.GoodsReceipt = i.GoodsReceipt
WHERE gr.GoodsReceipt = [Identifier])
SELECT
p.ProductId,
p.ProductDescription,
i.LotNumber,
STR(@Qty,5,2) AS Qty,
l.LocationId AS DefaultLocation
FROM GoodsReceipts AS gr
INNER JOIN Inventory AS i ON gr.GoodsReceipt = i.GoodsReceipt
INNER JOIN Products AS p ON i.Product = p.Product
INNER JOIN Locations AS l ON p.Location = l.location
WHERE gr.GoodsReceipt = [Identifier]
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
June 12, 2009 at 4:14 am
Tried that but did not run app side (as expected!).
Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
June 12, 2009 at 5:14 am
Hi Phil,
Change SELECT
p.ProductId,
p.ProductDescription,
i.LotNumber,
STR(@Qty,5,2) AS Qty,
l.LocationId AS DefaultLocation
FROM GoodsReceipts AS gr
to
SELECT
p.ProductId,
p.ProductDescription,
i.LotNumber,
STR(i.Quantity,5,2) AS Qty,
l.LocationId AS DefaultLocation
FROM GoodsReceipts AS gr
If the max. quantity is larger than 99.99 you need to change the length part of the STR function.
June 12, 2009 at 5:28 am
The reason the test code is always coming out as 10 is cause the variable is declared as an INT.
The reason your origianl query didn't work was because of syntax, not because of using cast or convert.
Try this :
DECLARE @Quantity DECIMAL (16,10)
SET @Quantity = 10.00000
SELECT @Quantity
--SELECT 'Qty: ' + CAST(CONVERT(@Quantity AS nvarchar(10) AS DECIMAL(5,2)) -- YOUR QUERY ERRORS
SELECT 'Qty: ' + CONVERT(nvarchar(10),CAST(@Quantity AS DECIMAL(5,2))) -- SHOULD BE
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
June 12, 2009 at 5:42 am
Thanks guys.
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
June 12, 2009 at 6:18 am
sqluser (6/12/2009)
Hi,test the date
DECLARE @test-2 int
SET @test-2 = 1000.00000
SELECT STR(@Test,5,2)
what happened?
ARUN SAS
you need to change the length 7 instead of 5.[/quote]
I don't think that is a good test.
When you declare an INT it's gonna truncate the .00000 and all you doing is adding two 0's to the end.
what happens when you have
1000.090000
you answer is always going to 1000.00
which I don't think is correct!?!
change the declaration from int to DECIMAL
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
June 12, 2009 at 6:26 am
STR function works with three parameters. The first parameter is the number with the decimal point. The second parameter is the total length of the string. The third parameter is the number of digits that are on the right side of the decimal point that will be in the string. The value of the third parameter is not added to the value of the second parameter, so in your case you asked for a string that will have 5 characters. From this 5 characters 2 can be after the decimal point, but getting all the digits that are left to the decimal point with the decimal point uses 5 digits, so there is no place to the digits after the decimal point. Since nothing comes after the decimal point, there is no reason to show the decimal point at all. If you’ll change the first parameter to 6 or above, you’ll get a different string.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply