Convert SQL Server Date to mm-yyyy

Question

I'm trying to convert my Date which is (eg. 2012-04-20 05:54:59) format in into mm-yyyy. I came across some solutions that says you would need to convert into varchar . Is there any way using the Convert function ?

Thanks :)

Answer 1

You can use FORMAT function, available from SQL Server 2012 onwards:

DECLARE @myDate DATETIME = '2012-04-20 05:54:59'
SELECT FORMAT(@myDate, 'MM-yyyy') 

Output:

04-2012

Answer 2

There might be a more graceful way to pull this off, but the below works.

Declare @dt datetime = GETDATE() 

SELECT LEFT('0' + CAST(MONTH(@dt) as varchar(2)),2)  + '-' + CAST(YEAR(@dt) as char(4))

btw my normal Date Conversion cheat sheet is here, but I'm not seeing MM-YYYY as one of the formats.