August 20, 2004 at 11:45 am
Hi everyone:
I'm getting files (A) with a column representing money as
(A) (B)
+002345 which equals to $23.45
or
-002345 which equals to -$23.45
How to convert varchar(50) data to the right decimal or money format like $23.45 or -$23.45? I could accept 23.45 or -23.45 as decimals. Important part, I have to convert that during INSERT INTO table B.
RGDS
Sam
August 20, 2004 at 12:15 pm
VARCHAR(50) will have the possibility of creating an overflow because the money data type can only hold up to 2^63 (922337203685477).
If that is not a problem I would recommend doing the following:
INSERT INTO B (field_name)
SELECT CAST(a.field_name AS MONEY) / 100 AS field_name
FROM a
Or if you need the two extra digits to the left of the decimal
INSERT INTO B (field_name)
SELECT CAST(SUBSTRING(a.field_name,1,LEN(a.field_name) - 2) + '.' + RIGHT(a.field_name,2) AS MONEY) AS field_name
FROM a
The second option will allow the varchar data to be a maximum of 92233720368547700 because it will be converted to 922337203685477.00 before the conversion to the money data type.
Morgan
August 27, 2004 at 10:05 am
Hi Morgan:
Thank you for your snippet. It does the trick.
RGDS
Sam
August 27, 2004 at 10:05 am
Hi Morgan:
Thank you for your snippet. It does the trick.
RGDS
Sam
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply