Change column from NVARCHAR to NBINARY

Question

I accidentally created a column with the wrong type NVARCHAR (for storing password salts), and I want to convert it to NVARBINARY .

I tried

 ALTER TABLE [dbo].[TableName] ALTER COLUMN [ColumnName] [varbinary] (20) NOT NULL GO 

but he says

Implicit conversion from nvarchar to varbinary data type is prohibited. Use the CONVERT function to run this query.

Is there any way to do this? CONVERT is presented only for expressions, not for changes.

Answer 1

The only way through the change would be as follows:

 Create Table a (id int,blb Nvarchar(10)) insert into a Values (1,'Test'), (2,N'Test2'); BEGIN Transaction ALTER TABLE a ADD blb_New [varbinary] (20) NULL GO UPDATE a SET blb_new = CAST(blb AS varbinary(20)) GO ALTER TABLE a DROP COLUMN blb GO EXEC sp_rename 'a.blb_new', 'blb', 'COLUMN' GO COMMIT Transaction Select *,CAST(blb as Nvarchar(20)) from a Drop Table a 

Answer 2

First you can convert all values ​​from NVARCHAR to NVARBINARY in the same column. After converting all the values, use the Alter table

You can link to the following link: Convert NVARCHAR (255) to DATE