Converting 1000 into 10.00

I have been given a Product table whoes all column types are varchar(8000)

One of the column is Price and other is DecimalPosition. Price column includes price without any decimal place and the data in DecimlaPosition column determins where the decimal should be placed.

So for instance, if the Price column includes '1000' and DecimalPosision includes '2' >> then it means that the actual price for this product is '10.00' and NOT '1000'. Similarly, if the DecimalPosision includes '3' >> then it means that the actual price for this product is '1.000' and NOT '1000'
My question is that when I am getting the price for a product from this table, how can I get the price in the correct format, e..g like '10.00' and not '1000'
Should I use SQL statements to convert 1000 into 10.00 or should I use some sort of programming logic to convert 1000 into 10.00.
kind regards

select substring(price,1,decimalPosition) + '.' + substring(price, 1 + decimalPosition, len(price) - decimalPosition)
from YOURTABLE

Nick

Edit: include decimal|||

There's a slight problem in Nick's code. It should be:

select substring(price, 1, len(price) - decimalPosition) + '.' + substring(price, len(price) - decimalPosition + 1, decimalPosition) as col1

However, no matter how you argue, that is just some flawed design. What I would strongly recommend is set up a migration "roll-out" plan for the data that is already in production and convert them to a standard price structure where you drop that decimalPlace column and correct the Price column into an real number.

Cheers,

Justin

Convert Int Values into words..?

Hi,

I'm doing an online invoice system with ASP and MS SQL.

So you got the price and total amount.

And let say the amount is $4000.00 , there is also a portion where the invoice needs to mention it in words, "Fourty Thousand"

Can it be "cast" ? "conver" ? or anything?
Or is there any workaround??$4000.00 = "Fourty Thousand"

I better should be careful while doing any transactions with you ;).

Maybe a function can be written to get it done ... will try it out ...

Highly unlikely you will get a readymade function for the same ...|||Opps..sorry...I was kindy sleepy..:)

thanks...I'll be trying out some code too...so many numbers condition to check in the if then else....|||don't write your own, there are so many scripts out there already, i'm sure you can adapt the logic, even if they aren't all written in wahtever language you plan to use...

http://developer.asna.com/Articles/b000106.asp
http://www.idinews.com/sourcecode/IntegerFunction.html
http://www.mvps.org/access/modules/mdl0001.htm
http://www.vbce.com/code/functions/convert_numbers_to_words/index.asp
http://www.vbexplorer.com/VBExplorer/tips/src36.asp

how did i find these so quickly?

google

convert data to currency

I have a column - datatype 'money' and my price variable is (for instance)
8450
how do I put this value into the money column without getting this error?

Microsoft OLE DB Provider for ODBC Drivers error '80040e07'

[Microsoft][ODBC SQL Server Driver][SQL Server]Disallowed implicit conversion from data type varchar to data type money, table 'db196009544.dbo196009544.vehicles', column 'price'. Use the CONVERT function to run this query.

ASP VBScript
I am using this syntax:

price = Upload.Form("price") '--this gives 8450--

SQL="UPDATE vehicles SET price='" & price & "' WHERE id=" & request.queryString("linkID") & ""

Thanks
markDo you define the variable as a daatype...sounds like it's defined as char if you are|||not sure!
I have tried using cCur() and various others when estting the variables but to no avail.

I suspect it has to be done in the SQL string, but can't make that work either.|||I mean it lloks like vb, and I'd ratyher keep my hands clean, buit can you do?

price = Upload.Form(price)

Also, you'd be way better off using stored procedures|||removing the single quotes from the price variable apears to have solved that but now the datetime variable is playing up in the same fashion, so I can't be sure if all is well with the price.
see my next post!