problem with CAST and CONVERT in SQL Server2000 converting decimal places from 4 to 2

All of my currency columns are only storing 2 decimal places when I insert into the database but when I pull out the data with a SELECT statement, I always get 4 decimal places instead of the 2 that were inserted.

For example:

Database Price SELECT statement Price

100.56 100.5600

I have tried to use the CAST and/or CONVERT commands but I cannot get the output to come out as 100.56. Has anyone had a similar problem?

Thanks

Look at the STR() function in Books Online.

STR(FloatExpression,LengthOverall,PlacesToRightOfDecimal)

|||Usually this type of formatting is done on the client side. You can use any of the string.format functions.|||You application is having a precision problem, it happens in SQL Server, there are two solutions a third party driver that on the TDS(Tabular Data Stream) level correct it or do it the cheap and free way use Decimal or Numeric instead of money and your problems will go away. Numeric is bigger than money but you will not get that problem. Hope this helps.|||I have tried to use the decimal datatype convert but it is rounding up or down and I am trying to show an amount, i.e. price. I just need something that will allow me to show the value as it exists in the database. When I view the data in the table, I only see the two decimal places.|||

Try the format in these links. Hope this helps

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconformattingnumericdataforspecificculture.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconcustomnumericformatstringsoutputexample.asp

Problem using CAST with Zeroed out Field Values

Hello --
I have a field with date values like: 20050714. When using SQL Server
to convert it to a Date in a View, I use the following:
CAST(MyDate as DateTime)

That works great. The problem is that some rows have the value:
00000000

When it hits one of these, I get a SQL Server Enterprise Manager Error:

Database Server: Microsoft SQL Server
Version: 08.00.0760
Runtime Error: [Microsoft][ODBC SQL Server Driver][SQL Server]The
conversion of a char data type to a datetime data type resulted in an
out-of-range datetime value.

Is there a workaround for this? Any assistance greatly appreciated.

RBollinger1) Quit storing dates as strings and numbers. This is the REAL answer.
And kill the guy who did this so he will not screw up the rest of the
database.

2) You did not give us specs; did you want the non-existent date
0000-00-00 to be a NULL or what? The error is exactly right ad shows
that your design has some serious problems. .

CAST (CASE @.my_string_date
WHEN '00000000'
THEN NULL -- or dummy value
ELSE @.my_string_date END AS DATETIME)

A better would be to do an update on the column, get rid of the
mythical dates and then replace that column with a temporal one after
validating the existing dates with a procedure. Don't just mop the
floor; fix the leak!|||robboll (robboll@.hotmail.com) writes:
> I have a field with date values like: 20050714. When using SQL Server
> to convert it to a Date in a View, I use the following:
> CAST(MyDate as DateTime)
> That works great. The problem is that some rows have the value:
> 00000000
> When it hits one of these, I get a SQL Server Enterprise Manager Error:
> Database Server: Microsoft SQL Server
> Version: 08.00.0760
> Runtime Error: [Microsoft][ODBC SQL Server Driver][SQL Server]The
> conversion of a char data type to a datetime data type resulted in an
> out-of-range datetime value.
> Is there a workaround for this? Any assistance greatly appreciated.

You can use the isdate() function to check whether a string is a
legal date value or not.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Combining your answer with the second guys answer, I think I will try
something like:

CAST(CASE@.my_string_date
WHEN isdate(my_string_date) = True
THEN NULL
ELSE @.my_string_date END AS DATETIME)

Thanks for your help!
RBollinger|||"isdate(my_string_date) = TRUE " is wrong; there is no Boolean datatype
or constants in SQL. You need a numeric value Did you fix the leak?|||--CELKO-- wrote:
> "isdate(my_string_date) = TRUE " is wrong; there is no Boolean
datatype
> or constants in SQL. You need a numeric value Did you fix the leak?

Yes I did "fix the leak". I ended up removing all invalid date values
as part of the append process -- the recommended solution.

Thanks.