March 21, 2004 at 10:37 pm
How to implement this which is in oracle to SQL Server
select to_char(800.00070474058253,'990.99999999EEEE') from dual
ouput : 8.00000705E+02
March 22, 2004 at 1:33 am
Hi,
Have a look at the convert function:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp
Hope this helps.
March 22, 2004 at 3:08 am
I don't think CONVERT or CAST functions can accomplish the desired output result, basically I believe this functions are useful for converting the output datatype, however I am wondering if we can format the output as posted in the above Oracle output.
If somebody has the solution, that would really help many a cause for reporting purpose.
Thanks
Prasad Bhogadi
www.inforaise.com
March 23, 2004 at 2:49 am
Hope this helps. Works only in sql server 2000
CREATE Function FormatNumber
(@RawNumber numeric(32,2) = 0,
@ShowDecimal as bit = 0
)
returns varchar(200) AS
BEGIN
declare @FormattedNumber as varchar(100)
declare @AfterDecimal as varchar(100)
declare @i as int
declare @j-2 as int
set @i = 0
set @j-2 = 0
set @FormattedNumber = convert(bigint, @RawNumber)
set @AfterDecimal = @RawNumber
if charindex('.', @AfterDecimal) 0
BEGIN
set @AfterDecimal = '.' + right(@AfterDecimal, len(@AfterDecimal)-charindex('.', @AfterDecimal))
END
ELSE
BEGIN
set @AfterDecimal = ''
END
while @i len(@FormattedNumber)
BEGIN
if @j-2 = 3
BEGIN
set @j-2 = -1
set @FormattedNumber = left(@FormattedNumber, len(@FormattedNumber)-@i) + ',' + right(@FormattedNumber, @i)
END
set @i = @i + 1
END
if @ShowDecimal 0
set @FormattedNumber = @FormattedNumber + @AfterDecimal
return @FormattedNumber
END
March 23, 2004 at 5:23 am
select stuff(convert(char(22),round(cast(800.00070474058253 as float),6),2),11,7,'')
March 23, 2004 at 8:40 am
Works great and this is the first time I looked at STUFFfunction in BOL after looking at your post. It is really a useful function.
Prasad Bhogadi
www.inforaise.com
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply