如何在 SQL Server 中获取 MMYYYY 格式？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5253768/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I get a MMYYYY format in SQL Server?
提问by Zach
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.
好的，这个问题有点误导......我知道有几种不同的方法可以从日期中获取 MMYYYY 格式，但它需要将字符串转换为 VARCHAR。虽然这一切都很好，但对结果进行排序却是一件非常痛苦的事情。
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...
结果如下：
1/2010
1/2011
10/2010
10/2011
11/2010
11/2011
12/2010
12/2011
2/2010
2/2011
3/2010
3/2011
等等...
I'm trying them to order by the date ascending. As you can see, they do not... Is there a way to get what I'm trying to achieve?
我正在尝试按日期升序排列。正如你所看到的，他们没有......有没有办法得到我想要实现的目标？
Thanks in advance!
提前致谢！
采纳答案by Martin Smith
;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)
回答by SQLMenace
what is wrong with
出什么问题了
ORDER BY TransactionDte DESC
or even
甚至
order by CONVERT(VARCHAR(4),YEAR(TransactionDte) + CONVERT(VARCHAR(2),MONTH(TransactionDte)) DESC
回答by Abe Miessler
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]
回答by FreeTheLemmings
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
