Data Conversion

Posted on 2012-09-05
Last Modified: 2012-09-06
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
Question by:olongus
    LVL 65

    Accepted Solution

    >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")
    LVL 25

    Expert Comment

    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

    LVL 9

    Expert Comment

    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

    Author Closing Comment

    Thanks jimhorn your solution worked perfectly....Thanks!!!!!!

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now