Missing Format() - Function

Hi folks,
i have to translate SQL-Statements from Access 2000 to SQL
Server 2000. There was a lot of Access-Functions used
(mid, str, iif ...) and i convert them to SQL-Server
Syntax, but i dont found a way to simulate the results of
the Format-Function with was used for numeric data
as "##0.##" or "##0 %" and so on ...
What is the best way to substitute the FORMAT() function
at SQL Server?
Thanks for any help.
Niels
On Fri, 15 Apr 2005 01:33:00 -0700, nieurig wrote:

>Hi folks,
>i have to translate SQL-Statements from Access 2000 to SQL
>Server 2000. There was a lot of Access-Functions used
>(mid, str, iif ...) and i convert them to SQL-Server
>Syntax, but i dont found a way to simulate the results of
>the Format-Function with was used for numeric data
>as "##0.##" or "##0 %" and so on ...
>What is the best way to substitute the FORMAT() function
>at SQL Server?
>Thanks for any help.
>Niels
Hi Niels,
The best way is to return unformatted data to the client and let the
client handle the formatting. Formatting is not a task that the server
is ideally suited for.
If you must do it at the server, then start by looking into functions
such as CONVERT, CAST and STR. If they don't do what you need, then post
some examples of unformatted data and how you want it to look, so that I
(and others) can look into it and try to come up with something.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||good morning hugo,
thanks for your comment.

>The best way is to return unformatted data to the client
and let the
>client handle the formatting. Formatting is not a task
that the server
>is ideally suited for.
Well i have to format the results, because the code of the
given application was lost and i translate the sql-
statements coming from a textfile.

>If you must do it at the server, then start by looking
into functions
>such as CONVERT, CAST and STR. If they don't do what you
need,
I use them for formating datetime values. It works well
but at numeric data the results a not good. therefore i
start to write a serverside function. I will post the code
if it was ready.
Niels
|||On Sat, 16 Apr 2005 00:30:57 -0700, nieurig wrote:

>good morning hugo,
>thanks for your comment.
>and let the
>that the server
>Well i have to format the results, because the code of the
>given application was lost and i translate the sql-
>statements coming from a textfile.
Hi Niels,
I might have misunderstood you. I thought you wanted to find a way to
control the format used for outputting numeric values from SQL Server,
but now I'm starting to think that you need to do the reverse: read in a
file with formatted results and make sure that they are converted to the
correct numeric values by SQL Server.
But maybe I am still misunderstanding you?

>into functions
>need,
>I use them for formating datetime values. It works well
>but at numeric data the results a not good. therefore i
>start to write a serverside function. I will post the code
>if it was ready.
Yes, you can do that. Or you can post some examples of typical data in
input / output pairs, to help me understand what exactly you need to get
done. I can only try to help if I know what you're trying to achieve,
and frankly, I don't know right now. Since I don't know what Access'
FORMAT() function does, you'll need to teach me (by example) - then I
can try to find a way to mimic this in SQL Server.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)