• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 605
  • Last Modified:

Data Conversion

I have a column with data type as int. I converted the int data type to varchar to convert nulls to N/A. As a result of this values such as 10000.00 have changed to 1000000. I need to convert the vaules back to money. Any help will be appreciated.


Isnull(Cast(Cast(PremiumValue as numeric) as varchar),'N/A') AS PremiumValue
1 Solution
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>I converted the int data type to varchar to convert nulls to N/A
>I need to convert the vaules back to money.

No can do Hoss, once you introduce a varchar value such as 'N/A' you can't do a 'have it money except if it's null,  then show that as 'N/A'.  Especially when you're going to have to perform math on the number later on.

It would be better to keep the column as an int (money?), and do any cosmetic-stuff such as N/A in whatever presentation tool you're displaying it.  

For example, as an SSRS expression...
=IIF(IsNothing(Fields!PremiumValue.Value), "N/A", Fields!PremiumValue.Value)

... or in an Access report ...
=Nz(PremiumValue, "N/A")
You say the value is 'int' ... I am pretty sure that int values do not have a decimal component.  So I am unsure how it started out as "10000.00".  And how it changed, on cast, to "1000000" ... a number 100 times larger also confuses me.  Is the decimal point implied?

If this is being done for display purposes ... it may be to do this is the presentation layer.  If you are planning on using the value for other calculations later on ... the "N/A" could cause issues.  This has all been said quite well by @jimhorn above.

If the first cast to decimal was an attempt to add decimal component before re-cast-ing it to varchar then look at this example.
declare @testtable table ( PremiumValue  int );
insert into @testtable values (100), (606), (921), (null);

select PremiumValue
     , Isnull(Cast(Cast(PremiumValue as numeric) as varchar),'N/A') AS PremiumValue2
     , Isnull(Cast(Cast(PremiumValue as numeric(8,2)) as varchar),'N/A') AS PremiumValue3
     , Isnull(Cast(Cast(PremiumValue as money) as varchar),'N/A') AS PremiumValue4
from @testtable

Open in new window

you have not changed the datatype in actual datatype right...

and if yu want it just for null you can easily achieve it using case statement no need to casrt here..

select case when isnull(fieldname,'')='' then 'N/A' else fieldname end as myfield from table_name
olongusAuthor Commented:
Thanks jimhorn your solution worked perfectly....Thanks!!!!!!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now