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 :)

Immaterialism

Try SELECT RIGHT(CONVERT(VARCHAR(10), GETDATE(), 105) , 7) – Tallie

I tried this but when I use varchar im not able to sort the date according to the year. for example say I transformed the date from 2015-04-20 05:54:59 to 04-2015, now, since this is in varchar format and say if there are more dates in the column where the dates were : 03-2011,06-2014. and if I sort this column, I will get the following: 03-2011,04-2015,06-2014. Sorting this varchar column doesn't sort the date column. – Immaterialism

Do the sorting on the column as a datetime value, just do this formatting related task to a column you'll use for display. – Barina

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

Intuition

Thank you :) . But here you've fed the date in the query. How would I frame the command if I had a date column instead ??? I tried the following, but didn't work : DECLARE @myDate (select date from dbo.EOD) SELECT FORMAT(@myDate, 'MM-yyyy') – Immaterialism

@PrateekDaniels If you want to format a specific column, e.g column [date], of a table then you can do: select FORMAT([date], 'MM-yyyy') from dbo.EOD – Intuition

@PrateekDaniels Happy to help. If this answer or any other one solved your issue, please mark it as accepted. – Intuition

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.

Roselynroseman

The correct would be RIGHT function, because when you're above September you'd want the two final numbers. – Buerger

So what happens in October/November/December? The month is 01?? – Butadiene

Answer 3

  select [MM-YYYY] = right(convert(varchar(10),getdate(),105),7)

Rosebud

I tried this but when I use varchar im not able to sort the date according to the year. for example say I transformed the date from 2015-04-20 05:54:59 to 04-2015, now, since this is in varchar format and say if there are more dates in the column where the dates were : 03-2011,06-2014. and if I sort this column, I will get the following: 03-2011,04-2015,06-2014. Sorting this varchar column doesn't sort the date column. – Immaterialism

Answer 4

As you are using SQL Server 2014, You can use FORMAT which is the best also you can apply this:

SELECT CONVERT(VARCHAR(2),MONTH(yourDateTimeField)) + '-' + 
       CONVERT(VARCHAR(4),YEAR(yourDateTimeField)) AS [MM-YYYY]
FROM yourTable
ORDER BY yourDateTimeField

Coburn

I tried this but when I use varchar im not able to sort the date according to the year. for example say I transformed the date from 2015-04-20 05:54:59 to 04-2015, now, since this is in varchar format and say if there are more dates in the column where the dates were : 03-2011,06-2014. and if I sort this column, I will get the following: 03-2011,04-2015,06-2014. Sorting this varchar column doesn't sort the date column. – Immaterialism

You just need to sort it based on your datetime field and display it as MM-YYYY. See my updated asnwer. – Coburn

Answer 5

I originally used the accepted answer, I needed it in an aggregate query. It was slow. This might be just a different way of doing it, but I like this one.

select right('00' + cast(month(@YOURDATE) as varchar), 2) + '-' + cast(year(@YOURDATE) as varchar)

Butadiene