MONEY FORMAT presentation

Hi all,
I am tryng to locate the SQLServer vesion of Informix's DBMONEY env variable
which is used in the Informix env by tools like ISQL and I4GL to format
values stored as type MONEY into a nice presentation pattern. Is there such a
property for SQLServer?
Why am I looking for this? Basically the application that we are building
handles all this preso stuff at the front-end, problem is we are trying to
rapid prototype a number of MIS reports (in SQLAnalyser) and the cllient is
used to seeing the numbers in a pre-formatted pattern as they currently use
ACCESS and the FORMAT function. Myself and my buddy are going slowly
blind/mad having to cast/convert values stored as money into strings to get
$'s and commas!
Slainte,
TaggartTaagart
DECLARE @.m AS DECIMAL (18,3)
SET @.m=100554545.36
SELECT convert(varchar,cast(@.m as money),1)
"Taggart" <Taggart@.discussions.microsoft.com> wrote in message
news:11E0F555-2BEF-4424-807C-3D6CEF31F6B7@.microsoft.com...
> Hi all,
> I am tryng to locate the SQLServer vesion of Informix's DBMONEY env
> variable
> which is used in the Informix env by tools like ISQL and I4GL to format
> values stored as type MONEY into a nice presentation pattern. Is there
> such a
> property for SQLServer?
> Why am I looking for this? Basically the application that we are building
> handles all this preso stuff at the front-end, problem is we are trying to
> rapid prototype a number of MIS reports (in SQLAnalyser) and the cllient
> is
> used to seeing the numbers in a pre-formatted pattern as they currently
> use
> ACCESS and the FORMAT function. Myself and my buddy are going slowly
> blind/mad having to cast/convert values stored as money into strings to
> get
> $'s and commas!
> Slainte,
> Taggart
>
>|||Hi Uri,
That's much better, but still missing the $'s. Apart from hardwiring the $
sign and then concatanating this to the result, any ideas?
What I don't get is the need to cast a column that is already set as money
(pity I didn't use decimal!?) to money, maybe we are missing something
fundamental but isn't this just consuming CPU resource for no apparent reason?
As ex-informix guys we are somewhat puzzled by this and, on a similar point,
all the manipulation one has to undertake for datetime columns when you only
want to use the date portion - presumably something to do with no date
datatype in SQLServer, which is something we find very strange both in the
additional manipulation and also in terms of datastorage?
Slainte,
Taggart|||On Sun, 4 Jun 2006 19:53:02 -0700, Taggart wrote:
>Hi Uri,
>That's much better, but still missing the $'s. Apart from hardwiring the $
>sign and then concatanating this to the result, any ideas?
Hi Taggart,
SQL Server is not intended to be used for formatting - that task is
usually handled by the front-end. As a result, SQL Server doesn't have
as much formatting features as some other tools.
If you're sure that your application is only used in dollar-using
countries, hardcoding the $ sign is probably the best solution. If the
app might be used all over the world, you'd be better off letting the
front-end determine the correct currency symbol from the useer's locale
settings and append that symbol to the amount.
>What I don't get is the need to cast a column that is already set as money
>(pity I didn't use decimal!?) to money, maybe we are missing something
>fundamental but isn't this just consuming CPU resource for no apparent reason?
There's no need for the extra CAST - Uri used it becuase the variable he
used in his example was not money. If your column is monmey, you can
just use
SELECT convert(varchar, Column_Name, 1)
>As ex-informix guys we are somewhat puzzled by this and, on a similar point,
>all the manipulation one has to undertake for datetime columns when you only
>want to use the date portion - presumably something to do with no date
>datatype in SQLServer, which is something we find very strange both in the
>additional manipulation and also in terms of datastorage?
Not having seperate date and time datatypes is indeed a pity.
However, there's no need for much manipulation to remove the time
portion from a datetime column. If you need the result as datetime (only
withoout time portion - or rather, with the default midnight time
portion), use
SELECT DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP), 0)
And if you need it in character format (for presentation purposes), use
CONVERT with an appropriate style parameter and define the length of the
result such that the time will be cut off, for instance
SELECT CONVERT(char(8), CURRENT_TIMESTAMP, 112)
--
Hugo Kornelis, SQL Server MVP|||Thanks Hugo your use of convert is much. much neater than my attempt!
"Hugo Kornelis" wrote:
> On Sun, 4 Jun 2006 19:53:02 -0700, Taggart wrote:
> >Hi Uri,
> >
> >That's much better, but still missing the $'s. Apart from hardwiring the $
> >sign and then concatanating this to the result, any ideas?
> Hi Taggart,
> SQL Server is not intended to be used for formatting - that task is
> usually handled by the front-end. As a result, SQL Server doesn't have
> as much formatting features as some other tools.
> If you're sure that your application is only used in dollar-using
> countries, hardcoding the $ sign is probably the best solution. If the
> app might be used all over the world, you'd be better off letting the
> front-end determine the correct currency symbol from the useer's locale
> settings and append that symbol to the amount.
> >What I don't get is the need to cast a column that is already set as money
> >(pity I didn't use decimal!?) to money, maybe we are missing something
> >fundamental but isn't this just consuming CPU resource for no apparent reason?
> There's no need for the extra CAST - Uri used it becuase the variable he
> used in his example was not money. If your column is monmey, you can
> just use
> SELECT convert(varchar, Column_Name, 1)
> >
> >As ex-informix guys we are somewhat puzzled by this and, on a similar point,
> >all the manipulation one has to undertake for datetime columns when you only
> >want to use the date portion - presumably something to do with no date
> >datatype in SQLServer, which is something we find very strange both in the
> >additional manipulation and also in terms of datastorage?
> Not having seperate date and time datatypes is indeed a pity.
> However, there's no need for much manipulation to remove the time
> portion from a datetime column. If you need the result as datetime (only
> withoout time portion - or rather, with the default midnight time
> portion), use
> SELECT DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP), 0)
> And if you need it in character format (for presentation purposes), use
> CONVERT with an appropriate style parameter and define the length of the
> result such that the time will be cut off, for instance
> SELECT CONVERT(char(8), CURRENT_TIMESTAMP, 112)
> --
> Hugo Kornelis, SQL Server MVP
>