Alter 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 it says

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

Is there a way to do that? CONVERT seems to be for expressions only, not for alterations.

Answer 1

The only way by altering will be someting like:

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