从 SQL Server 2008 中的日期时间字段格式化月份和年份
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15814318/
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
Formatting the Month and Year from a datetime field in SQL Server 2008
提问by coopertkm
I need to display only the month and year from a Datetimefield in SQL Server 2008.
我只需要从DatetimeSQL Server 2008 中的字段中显示月份和年份。
This should be formatted in the following way:
这应该按以下方式格式化：
Jan 13
Feb 13
March 13
etc
等等
I also need to group by and order by these results
我还需要对这些结果进行分组和排序
I've tried many permutations of Cast, Convert, DatePartetc but can't quite get what I need.
我已经尝试了,等的许多排列Cast，但不能完全得到我需要的。ConvertDatePart
Any suggestions?
有什么建议？
回答by Lamak
SELECT MonthYear
FROM ( SELECT CONVERT(CHAR(6),YourDate,112) MonthYearOrder,
CONVERT(CHAR(6),YourDate,107) MonthYear
FROM YourTable
GROUP BY CONVERT(CHAR(6),YourDate,112),
CONVERT(CHAR(6),YourDate,107)) A
ORDER BY MonthYearOrder
回答by IndoKnight
You can use a utility function something like this
您可以使用这样的实用程序功能
回答by Doan Cuong
SELECT (left(convert(char(11), date, 107), 3) + " " +
right(convert(char(11), date, 107), 2)) as date from
(
select date from yourtable
group by date
order by year(date) asc, month(date)asc
) as a
Convert 107will give you the format MMM dd, yyyy, you only need month and last 2 digits of year so we'll use leftfunction to get MMMand right function to get last 2 digits of year
Convert107将为您提供格式MMM dd, yyyy，您只需要月份和年份的最后 2 位数字，因此我们将使用left函数来获取MMM和正确的函数来获得年份的最后 2 位数字
Update:add support for sorting and grouping. And you can even add where clause into sub query to conditioning
更新：添加对排序和分组的支持。您甚至可以将 where 子句添加到子查询中以进行条件处理
回答by Laurent S.
I would try this :
我会试试这个：
select convert(char(3), yourdatefield, 0) + " " + (YEAR( yourdatefield ) % 100) from yourtable
