Geek asks and answers

How can I get the MMYYYY format in SQL Server?

Well, the question is mildly misleading ... I know a couple of different ways to get the MMYYYY format from a date, but this requires converting the string to VARCHAR. While everything is beautiful and dandy, ordering the results, where it becomes a real pain.

Here is what I use:

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 are 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 try to sort them by date in ascending order. As you can see, they are not ... Is there a way to get what I'm trying to achieve?

Thanks in advance!

+3
4
;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)
+2

ORDER BY TransactionDte DESC


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

:

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

/, :

    REPLACE(RIGHT(CONVERT(VARCHAR(10), TransactionDte, 103), 7),'/','') AS [MMYYYY]
+3
source 
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
0
source

More articles:


All Articles