April 4, 2006 at 9:26 am
select '$ '+ convert(varchar(30), sum(lu.Private)) as Private from ......
I need to insert this private into numeric data type?? Thanks!
April 4, 2006 at 10:03 am
What does the result of
'$ '+ convert(varchar(30), sum(lu.Private))
look like when done? Is it $1.00 or something like that?
If so you might consider using the Money datatype.
April 4, 2006 at 10:18 am
Thanks but I cannot change the data-type from numeric to money on the destination table. the value is 15610 I need to add '$'.
April 4, 2006 at 10:24 am
what's wrong with the result you get from your "select" ?!?!
**ASCII stupid question, get a stupid ANSI !!!**
April 4, 2006 at 10:34 am
I need to insert that result into a column that has numeric data-type... which I was not able.
April 4, 2006 at 10:40 am
If you cannot change the data type to money/smallmoney then you can't do it...why do you need to store it in this format ?!?!
Usually formats are used for display...
**ASCII stupid question, get a stupid ANSI !!!**
April 4, 2006 at 10:44 am
client need the numbers to be displayed this way and our database has numeric type for that column.. i guess we need to change the data-type if the client is too adamant about it. Thanks Sushila
April 4, 2006 at 10:48 am
Then just store as the numeric itself. However the application or a output will need to ad the $, you cannot store with the $ in the current field.
April 4, 2006 at 10:54 am
Placing the "$" character in front of a formatted number makes it a character value, which is not a number, and cannot be stored as a number.
Formatting rules are not data storage rules, and generally do not belong in the data. How the data looks when it gets to the user is the responsibility of the application pulling the data from the server, be it a reporting tool, application, export script, whatever.
Changing the data type to money will not put the "$" character with the value, it just tips off some clients to display the data that way. Change the application, not the data.
Eddie Wuerch
MCM: SQL
April 4, 2006 at 11:02 am
Just wanted to comment on the "Changing the data type to money will not put the "$" character with the values..."...
An example from BOL..
CREATE TABLE TestMoney (cola INT PRIMARY KEY, colb MONEY)
GO
SET NOCOUNT ON
GO
-- The following three INSERT statements work.
INSERT INTO TestMoney VALUES (1, $123.45)
GO
INSERT INTO TestMoney VALUES (2, $123123.45)
GO
INSERT INTO TestMoney VALUES (3, CAST('$444,123.45' AS MONEY) )
GO
select colb from TestMoney
drop table TestMoney
Results: (when picking up from regional settings...):
$123.45
$123,123.45
$444,123.45
**ASCII stupid question, get a stupid ANSI !!!**
April 4, 2006 at 11:07 am
Thanks all guys!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply