If I run the following I was hoping for the same result from both final
queries. The code below isn't what I am actually doing, what I'm actually
doing is putting values into a 'min' column of a data analysis table, and I
want to be able to put more than just numbers (for example, minimum dates) s
o
it should be easiest if that column is a varchar(100). But the error I'm
dealing with shows up with the following minimal code:
drop table #Values
go
create table #Values (value money)
go
insert into #Values Values (1)
insert into #Values Values (-21705503.4616)
go
select min(value) from #Values
select CAST(min(value) as varchar(100)) from #Values
The last two decimal places drop on the cast... That later ruins some stuff
I'm doing, I need all the decimal places to be there.
Any ideas? Am I missing something obvious or is this a SQL Server error?
It's such a simple thing really...
Thanks!First question: why are you converting from money to a varchar?
Second question: do you really need to use the money data type?
The money datattype is useful if you are storing currency delimiters
with your numeric data; if not, you can use numeric or decimal instead.
Stu|||WHOOPS.
Forgot to mention that if you must convert money to a character
datatype, use the CONVERT statement (not CAST) because it allows you to
specify a style.
>From the Books Online:
Value Output
0 (default) No commas every three digits to the left of the decimal
point, and two digits to the right of the decimal point; for example,
4235.98.
1 Commas every three digits to the left of the decimal point, and two
digits to the right of the decimal point; for example, 3,510.92.
2 No commas every three digits to the left of the decimal point, and
four digits to the right of the decimal point; for example, 4235.9819.
In your case,
SELECT CONVERT(varchar(100), MIN(value), 3) FROM #Values
HTH,
Stu|||Like I said above :) I have to use varchar because I have a column that is
going to have minimums of lots of different datatypes including datetime.
However, later I get values from this table and convert them back to their
real datatypes. It works in all cases except this.
This is an ETL process, the source data column is money, I don't have the
opportunity to change it. I am similarly not excited about money, I usually
strictly use numeric(9,2), numeric(9,4), numeric(19,2) numeric(19,4) and
with 6, etc. instead. I work for Company B who is doing the project for
Company A, so I definitely can't change the column type!!
So you get the same result running the code? If everyone gets the same
result seems like a bug to me.
"Stu" wrote:
> First question: why are you converting from money to a varchar?
> Second question: do you really need to use the money data type?
> The money datattype is useful if you are storing currency delimiters
> with your numeric data; if not, you can use numeric or decimal instead.
>
> Stu
>|||IMHO a more appropriate alternative would be to use the sql_variant datatype
,
if you really must store the values in a single column. It also 'remembers'
the original datatype.
That is, if you can live with the whole 8kB worth of sql-variant length.
ML
http://milambda.blogspot.com/|||Cool. So you are suggesting this line:
select convert(varchar(100), min(value), 2) from #Values
appended to the end of the sql I sent. That seems to work. This is
incredibly annoying because I now have to do this conditionally on the sourc
e
column data type. My actual code that does the min is inside some dynamic
sql and fires tens of times, not just once... Ughh... I can do it, though.
Microsoft: Update Cast on money->varchar so it will catch all decimal places
for money, make everyone's life easier :). Make default behavior no commas
and all significant figures beyond the decimal.
Thanks Stu!
"Stu" wrote:
> WHOOPS.
> Forgot to mention that if you must convert money to a character
> datatype, use the CONVERT statement (not CAST) because it allows you to
> specify a style.
>
> Value Output
> 0 (default) No commas every three digits to the left of the decimal
> point, and two digits to the right of the decimal point; for example,
> 4235.98.
> 1 Commas every three digits to the left of the decimal point, and two
> digits to the right of the decimal point; for example, 3,510.92.
> 2 No commas every three digits to the left of the decimal point, and
> four digits to the right of the decimal point; for example, 4235.9819.
>
> In your case,
> SELECT CONVERT(varchar(100), MIN(value), 3) FROM #Values
>
> HTH,
> Stu
>|||That sounds
- so then the conversion is implicit and I would just updat
e
the column with no cast or convert? I'll play with that. That would be
great.
I am putting in the avg, min, max, stdev and other statistical information
about columns in this table, so it is not large although my statistical
analysis sp runs over tables that are quite large. The result table is no
bigger than 50X100, so 8kB is nothing.
Thanks!
"ML" wrote:
> IMHO a more appropriate alternative would be to use the sql_variant dataty
pe,
> if you really must store the values in a single column. It also 'remembers
'
> the original datatype.
> That is, if you can live with the whole 8kB worth of sql-variant length.
> ML
> --
> http://milambda.blogspot.com/|||It worked - I just changed the column data type in my 'summary table' and
touched no other code. Which is good as this is a 1000+ line stored
procedure and is really annoying to update...
"ML" wrote:
> IMHO a more appropriate alternative would be to use the sql_variant dataty
pe,
> if you really must store the values in a single column. It also 'remembers
'
> the original datatype.
> That is, if you can live with the whole 8kB worth of sql-variant length.
> ML
> --
> http://milambda.blogspot.com/|||Test well before use. :)
Also look up the SQL_VARIANT_PROPERTY system function in Books Online.
ML
http://milambda.blogspot.com/|||Hi
drop table #Values
go
create table #Values (value decimal(18,4))
go
insert into #Values Values (1)
insert into #Values Values (-21705503.4616)
go
select min(value) from #Values
select CAST(min(value) as varchar(100)) from #Values
"LightMiner" <LightMiner@.discussions.microsoft.com> wrote in message
news:61A8ED8F-08D2-456B-A540-B1EE1248902E@.microsoft.com...
> It worked - I just changed the column data type in my 'summary table' and
> touched no other code. Which is good as this is a 1000+ line stored
> procedure and is really annoying to update...
> "ML" wrote:
>
Phil Brammer wrote: