How can I get a MMYYYY format in SQL Server?

Okay, the question is mildly misleading... I know a couple different ways to get the MMYYYY format from a date, but it requires converting the string into VARCHAR. While that's all fine and dandy, ordering the results is where it becomes a real pain.

Here's what I'm using:

SELECT  
CONVERT(VARCHAR(2),MONTH(TransactionDte)) + '/' + CONVERT(VARCHAR(4),YEAR(TransactionDte) AS MMYYYY  
,SUM(TransactionCt) AS TransCt  
,SUM(TransactionAmt) AS TransAmt  
FROM Transactions  
GROUP BY CONVERT(VARCHAR(2),MONTH(TransactionDte)) + '/' + CONVERT(VARCHAR(4),YEAR(TransactionDte)

The results appear as follows:

1/2010

1/2011

10/2010

10/2011

11/2010

11/2011

12/2010

12/2011

2/2010

2/2011

3/2010

3/2011

etc...

I'm trying them to order by the date ascending. As you can see, they do not... Is ther开发者_开发问答e a way to get what I'm trying to achieve?

Thanks in advance!

---

what is wrong with

ORDER BY TransactionDte DESC

or even

order by  CONVERT(VARCHAR(4),YEAR(TransactionDte) + CONVERT(VARCHAR(2),MONTH(TransactionDte)) DESC

---

Try this method:

    RIGHT(CONVERT(VARCHAR(10), TransactionDte, 103), 7) AS [MM/YYYY]

If you want it without the / then use this:

    REPLACE(RIGHT(CONVERT(VARCHAR(10), TransactionDte, 103), 7),'/','') AS [MMYYYY]

---

;WITH t AS
(
SELECT GETDATE() AS TransactionDte UNION ALL 
SELECT GETDATE()+1 AS TransactionDte UNION ALL 
SELECT GETDATE()+90
)
SELECT CONVERT(VARCHAR(2),MONTH(TransactionDte)) + '/' + 
       CONVERT(VARCHAR(4),YEAR(TransactionDte)) AS MMYYYY,
       COUNT(*)
FROM t
GROUP BY MONTH(TransactionDte), YEAR(TransactionDte)
ORDER BY MIN(TransactionDte) 

---

SELECT
MM/YYYY
,TransCt
,TransAmt
FROM
(SELECT  
DATEPART(MM, TransactionDte) AS TransMonth
,DATEPART(YYYY,TransactionDte) AS TransYear
,RIGHT(CONVERT(VARCHAR(10), TransactionDte, 103), 7) AS [MM/YYYY]
,SUM(TransactionCt) AS TransCt  
,SUM(TransactionAmt) AS TransAmt  
FROM Transactions  
GROUP BY 
DATEPART(MM, TransactionDte) 
,DATEPART(YYYY,TransactionDte) AS TransYear
,RIGHT(CONVERT(VARCHAR(10), TransactionDte, 103), 7)
) T
ORDER BY TransYear,TransMonth