SQL 将日期时间转换为数字和年份中的月份
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/30162956/
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
Convert datetime to month in number and year
提问by Sven Müller
I have a datetime e.g. 2014-04-24 00:28:53.897 and want to convert this in a select query to month in number and year - 4 2014.
我有一个日期时间，例如 2014-04-24 00:28:53.897，并希望在选择查询中将其转换为数字和年份的月份 - 4 2014。
回答by Chanom First
Try this
尝试这个
SELECT FORMAT(GETDATE(),'MM yyyy')
replace getdate with you datetime column
用你的日期时间列替换 getdate
Hope it help you.
希望对你有帮助。
回答by Matt
Use DATEPART
使用日期部分
SELECT CONCAT(DATEPART(mm,datefield),' ',DATEPART(yyyy,datefield)) AS monthyear
FROM yourtable
SQL FIDDLE: http://sqlfiddle.com/#!6/9c896/7/0
SQL 小提琴：http://sqlfiddle.com/#!6/9c896/7/0
回答by CeOnSql
you can use something like this: (where you replace GETDATE() with your date/datetime column)
你可以使用这样的东西：（用你的日期/日期时间列替换 GETDATE() ）
SELECT CAST(DATEPART(MONTH,GETDATE()) AS VARCHAR(2)) + ' ' + CAST(DATEPART(YEAR,GETDATE()) AS VARCHAR(4))
回答by Rahul Parit
You can use the following on SQL Server 2012 and above (or others that provide CONCAT):
您可以在 SQL Server 2012 及更高版本（或其他提供CONCAT）上使用以下内容：
SELECT CONCAT(DATEPART(mm,dateField),' ',DATEPART(yyyy,dateField))
AS MONTH_YEAR
FROM TABLENAME;
回答by Neeraj Sharma
Set into a format. declare @d datetime='2014-04-24 00:28:53.897' select FORMAT(@d, 'MM yyyy')
设置成格式。声明@d datetime='2014-04-24 00:28:53.897' 选择格式(@d, 'MM yyyy')
回答by xerius
/* sql code */
--if you have datetime as text use this to convert to date
--select cast('2014-04-24 00:28:53.897' as date)
select convert(varchar(2), month(cast('2014-04-24 00:28:53.897' as date))) + ' ' + convert(char(4), year(cast('2014-04-24 00:28:53.897' as date)))
--if you have datetime field already use this
select convert(varchar(2), month(getdate())) + ' ' + convert(char(4), year(getdate()))
回答by Imran Ali Khan
Use DATEPART For this
为此使用 DATEPART
SELECT Convert(VARCHAR(10),DATEPART(mm,yourfield),111) + ' ' +
Convert(VARCHAR(10),DATEPART(yyyy,yourfield),111) AS outputmonthyear
FROM yourtableName
http://sqlfiddle.com/#!6/fa887/7
http://sqlfiddle.com/#!6/fa887/7
DATEPART Definition and Usage
DATEPART 定义和用法
The DATEPART() function is used to return a single part of a date/time, such as year, month, day, hour, minute, etc.
DATEPART() 函数用于返回日期/时间的单个部分，例如年、月、日、小时、分钟等。
Syntax DATEPART(datepart,date) Where date is a valid date expression.
语法 DATEPART(datepart,date) 其中 date 是一个有效的日期表达式。
for more details please Visit
欲了解更多详情，请访问
