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.

[quoted text, click to view]

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
--

[quoted text, click to view]

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?


[quoted text, click to view]

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
--

good morning hugo,
thanks for your comment.

[quoted text, click to view]

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.

[quoted text, click to view]
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