Change data type in a SQL Server view

I have a view called dbo.NpsResultsView.

The code for this view is:

SELECT        
    dbo.CRMDeals.dealid,
    dbo.CRMDeals.contact,
    dbo.CRMContacts.title,
    dbo.CRMContacts.firstname,
    dbo.CRMContacts.lastname, 
    dbo.CRMContacts.dealCustomFieldId,
    dbo.CRMCustomFieldsData.fieldValue,
    dbo.CRMCustomFieldsData.updatedTimestamp, 
    dbo.ClientsMigrationDataset.Groups AS Expr1, 
    dbo.ClientsMigrationDataset.Client,
    dbo.ClientsMigrationDataset.Email,
    dbo.ClientsMigrationDataset.ClientManager
FROM
    dbo.CRMDeals
INNER JOIN    
    dbo.CRMContacts ON dbo.CRMContacts.contactid = dbo.CRMDeals.contact 
INNER JOIN    
    dbo.CRMDealCustomFieldsData ON dbo.CRMDealCustomFieldsData.dealId = dbo.CRMDeals.dealid 
INNER JOIN 
    dbo.ClientsMigrationDataset ON dbo.ClientsMigrationDataset.Email = dbo.CRMContacts.email
WHERE        
    (dbo.CRMDealCustomFieldsData.dealCustomFieldId = '15')

In that view, I have a column called fieldValue which is returned as VarChar, and this needs to be int as it's a score out of 10.

Note: using SQL Server Management Studio.

Use CAST(fieldValue AS INT) in your view definition - or if you want to avoid an exception when a value in fieldValue isn't really an INT, maybe use TRY_CAST(fieldValue AS INT). Then any "improper" value will be replaced by NULL instead of throwing an exception.

Try this:

CREATE VIEW dbo.NpsResultsView
AS
    SELECT        
        dbo.CRMDeals.dealid,
        dbo.CRMDeals.contact,
        dbo.CRMContacts.title,
        dbo.CRMContacts.firstname,
        dbo.CRMContacts.lastname, 
        dbo.CRMContacts.dealCustomFieldId,
        TRY_CAST(dbo.CRMCustomFieldsData.fieldValue AS INT) AS 'fieldValueInt',
        dbo.CRMCustomFieldsData.updatedTimestamp, 
        dbo.ClientsMigrationDataset.Groups AS Expr1, 
        dbo.ClientsMigrationDataset.Client,
        dbo.ClientsMigrationDataset.Email,
        dbo.ClientsMigrationDataset.ClientManager
    FROM
        dbo.CRMDeals
    INNER JOIN    
        dbo.CRMContacts ON dbo.CRMContacts.contactid = dbo.CRMDeals.contact 
    INNER JOIN    
        dbo.CRMDealCustomFieldsData ON dbo.CRMDealCustomFieldsData.dealId = dbo.CRMDeals.dealid 
    INNER JOIN 
        dbo.ClientsMigrationDataset ON dbo.ClientsMigrationDataset.Email = dbo.CRMContacts.email
    WHERE         
        (dbo.CRMDealCustomFieldsData.dealCustomFieldId = '15')

Note that you cannot use this command to change the definition for a view. To change the view definition, you must drop the view and then recreate it.

CREATE VIEW AView
AS
SELECT CAST(title AS char(50))
FROM titles

You can use cast and covert function to change a datatype