Change data type in a SQL Server view

Question

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.

Answer 1

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