asked on

can one column hold different data types?

i think my answer is no. this is the situtation:
i have one column that now is "money". But i have data that i want to hold in this column that's percentage as well. right now, when i display this column thru my code...i see the percentage as $:

select total from tableand when i display it in code..i see:

My amount: $6000
My percentage: $8.1 (this should be 8.1%)

--- i thought about having it as varchar, then casting it. i thought about having a case statement...
case when "money" then <cast value as money>
when "percentage" then <cast value as percentage>

but not sure..any ideas/

momi_sabag

the casting would work,
the question is, who puts the $ sign there
that is probably done by the client you use and not by the sql server engine

ASKER CERTIFIED SOLUTION

momi_sabag

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Camillia

ASKER

i think the client puts $. Not sure tho..wouldnt "money" field put $ automatically? have to look into it...

but still with sql_variant, we have to "cast". it's ok tho. I'll look into it.

Camillia

ASKER

what's wrong with this case when i try to covert?? get an error with percent

select
case coltype when 'A' then convert(money,colvalue)
when 'P' then convert(percent,colvalue)
else colvalue
end
from testtable

SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Camillia

ASKER

yes, silly me, just looked at BOL. let me try your method. Kamila,