Numeric Conversion - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Numeric Conversion

Author

Topic

ankur_gurha
Starting Member

20 Posts

Posted - 2006-08-01 : 05:29:37

Thanks guys for answering all my silly questions, but i am learning.. so hope u all can understand.

My other question is i need to convert

CostOfSales = 1785.95
which i retreive as
CAST ( 0 AS decimal(10,2) ) As 'CostOfSales'

into 1,785.95 format so basically its got to be in format where we have comma's seprating the result according to the number...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-01 : 05:33:09

perform the formatting of numbers in your front end application. It is much much easier to do it there

ankur_gurha
Starting Member

20 Posts

Posted - 2006-08-01 : 05:35:38

Actually its a report application where we just.. slap the database results on to an excel spreadsheet so i need to do the foramting in the sql. Please suggest..

Thanks..in advance

Cheers!!
A..

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-08-01 : 05:41:29

convert(money, yourValue, 1)

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-01 : 05:48:10

[code]-- prepare test data
declare @examples table
(
i varchar(50),
j float,
k decimal(38,6)
)

insert @examples
select '0', 0, 0 union all
select '1785.95', 1785.95, 1785.95 union all
select '-13.1', -13.1, -13.1 union all
select '25', 25, 25 union all
select '234293234.123456', 234293234.123456, 234293234.123456

-- check out varchar originals
select i,
convert(varchar, cast(i as money), 1) Peso,
convert(money, i, 1) spirit1
from @examples

-- check out float originals
select j,
convert(varchar, cast(j as money), 1) Peso,
convert(money, j, 1) spirit1
from @examples

-- check out decimal originals
select k,
convert(varchar, cast(k as money), 1) Peso,
convert(money, k, 1) spirit1
from @examples[/code]Or you want to convert 1,798.34 to 1798.34?

Peter Larsson
Helsingborg, Sweden

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-01 : 05:49:47

"excel spreadsheet"
This type of formating will be a piece of cake for excel.

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-08-01 : 09:37:58

quote:
Originally posted by ankur_gurha

Actually its a report application where we just.. slap the database results on to an excel spreadsheet so i need to do the foramting in the sql.

Then you absolutely do NOT want to do the formatting in SQL, which can only be done by converting the numeric value into a formatting string that will be useless for any calculations or sorting in Excel.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-01 : 21:42:42

quote:
Originally posted by blindman

quote:
Originally posted by ankur_gurha

Actually its a report application where we just.. slap the database results on to an excel spreadsheet so i need to do the foramting in the sql.
Then you absolutely do NOT want to do the formatting in SQL, which can only be done by converting the numeric value into a formatting string that will be useless for any calculations or sorting in Excel.

unless you plan to convert back to numeric / numbers in Excel

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-08-02 : 07:31:23

[code]

DECLARE @Foo varchar(1000)

SELECT @Foo = 'Actually its a report application where we just.. slap the database results on to an excel spreadsheet so i need to do the foramting in the sql. Please suggest..'

SELECT
RT.WhatYouSaid
FROM
RealityTable RT
JOIN
BSTable BS
ON
RT.WYS_ID = BS.WYS_ID
WHERE
BS.WhatYouSaid = @Foo

RESULTS:
The professor said to do the formatting in SQL to see if we learned anything in the past two weeks in class.
(1 row(s) affected)
[/code]

Yea, this is before my coffee. Sorry

For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-08-02 : 10:50:28

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources