Trying to update Date Format from (DD-MM-YYYY) to (MM-YYYY) SQL

I’m trying to update my date column which is (eg. 2012-04-20) format in into mm-yyyy. I searched for solutions that you can do by converting to varchar however it gave me an error

Msg 241, Level 16, State 1, Line 4
Conversion failed when converting date and/or time from character string.

This is the code I’m executing

UPDATE dbo.DimDate 
SET FullDateAlternateKey = RIGHT(CONVERT(varchar(10), GETDATE(), 105), 7)

Any idea how to solve this?

Edit:

The problem is not only to display but to update the column’s formatting into MM-YYYY

Answer

I’m trying to update my date column which is (eg. 2012-04-20) format in into mm-yyyy.

You cannot do this. DATEs are stored using an internal format, which you cannot control. What you can control is how the data is presented when presented as a string.

One method to do what you want is to use a computed column:

ALTER TABLE dbo.DimDate ADD FullDateAlternateKey_mmyyyy AS
    (FORMAT(FullDateAlternateKey, 'MM/yyyy'));

You can then reference FullDateAlternateKey_mmyyyy and it always has the date value as a string in the format that you want.