Access to SQL Server conversion - format number question

In Access the following sql
select Format(0.12345,"#.00###") from tblA
returns
0.12345
select Format(0.123,"#.00###") from tblA
returns
0.123
In SQL Server
select convert(decimal(6,5),0.12345)
returns
0.12345
But, select convert(decimal(6,5),0.123)
returns
0.12300 instead of 0.123
Is there a way in SQL Server to convert the number so that if the number of
character after decimal point is 2, show only those 2 without training 0's,
and if the number of character after decimal point is 5 shows all the 5
numbers ?
Thanks.Paul fpvt2 wrote:
> In Access the following sql
> select Format(0.12345,"#.00###") from tblA
> returns
> 0.12345
> select Format(0.123,"#.00###") from tblA
> returns
> 0.123
> In SQL Server
> select convert(decimal(6,5),0.12345)
> returns
> 0.12345
> But, select convert(decimal(6,5),0.123)
> returns
> 0.12300 instead of 0.123
> Is there a way in SQL Server to convert the number so that if the number o
f
> character after decimal point is 2, show only those 2 without training 0's
,
> and if the number of character after decimal point is 5 shows all the 5
> numbers ?
> Thanks.
Your client application determines how numbers are displayed, not SQL
Server. You should refer to the formatting capabilities of whatever
client application or language you are using. Unfortunately you didn't
specify that so we can't easily help you with it.
To have control over the formatting from SQL you'd have to return a
string rather than a number, which is not usually recommended.
David Portas
SQL Server MVP
--|||Thank you.
> To have control over the formatting from SQL you'd have to return a
> string rather than a number, which is not usually recommended.
I am sorry, I posted the message incorrectly, it should be string, not a
number, like the following:
in Access:
select Format('0.123',"#.00###") from tblA
returns
0.123
in SQL Server:
But, select convert(decimal(6,5),'0.123')
returns
0.12300 instead of 0.123
The language we are using is VB, but unfortunately the VB application
expecting the result of the query already formatted.
Is there a way to do the above in SQL Server ?
Thank you.
"David Portas" wrote:

> Paul fpvt2 wrote:
> Your client application determines how numbers are displayed, not SQL
> Server. You should refer to the formatting capabilities of whatever
> client application or language you are using. Unfortunately you didn't
> specify that so we can't easily help you with it.
> To have control over the formatting from SQL you'd have to return a
> string rather than a number, which is not usually recommended.
> --
> David Portas
> SQL Server MVP
> --
>