从 SQL DATE 只获取月份和年份
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1781946/
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
Getting only Month and Year from SQL DATE
提问by user160820
I need to access only Month.Year from Date field in SQL Server.
我只需要从 SQL Server 中的日期字段访问 Month.Year。
回答by MatBailie
As well as the suggestions given already, there is one other possiblity I can infer from your question:
- You still want the result to be a date
- But you want to 'discard' the Days, Hours, etc
- Leaving a year/month only date field
除了已经给出的建议之外，我还可以从您的问题中推断出另一种
可能性：- 您仍然希望结果是日期
- 但您想“丢弃”天数、小时数等
- 离开一年/月只有日期字段
SELECT
DATEADD(MONTH, DATEDIFF(MONTH, 0, <dateField>), 0) AS [year_month_date_field]
FROM
<your_table>
This gets the number of whole months from a base date (0) and then adds them to that base date. Thus rounding Down to the month in which the date is in.
这从基准日期 (0) 获取整月数，然后将它们添加到该基准日期。因此四舍五入到日期所在的月份。
NOTE: In SQL Server 2008, You will still have the TIME attached as 00:00:00.000 This is not exactly the same as "removing" any notation of day and time altogether. Also the DAY set to the first. e.g. 2009-10-01 00:00:00.000
注意：在 SQL Server 2008 中，您仍然会将 TIME 附加为 00:00:00.000 这与完全“删除”任何日期和时间符号并不完全相同。也将 DAY 设置为第一个。例如 2009-10-01 00:00:00.000
回答by Lucas Ayala
select month(dateField), year(dateField)
回答by Renne007
SELECT convert(varchar(7), getdate(), 126)
You might wanna check out this website: http://anubhavg.wordpress.com/2009/06/11/how-to-format-datetime-date-in-sql-server-2005/
你可能想看看这个网站：http: //anubhavg.wordpress.com/2009/06/11/how-to-format-datetime-date-in-sql-server-2005/
回答by Adriaan Stander
SELECT DATEPART(yy, DateVal)
SELECT DATEPART(MM, DateVal)
SELECT DATENAME(MM, DateVal)
回答by Valya Sylevych
datename(m,column)+' '+cast(datepart(yyyy,column) as varchar) as MonthYear
the output will look like: 'December 2013'
输出将如下所示：'December 2013'
回答by Grzegorz Gierlik
回答by NoNaMe
This can be helpful as well.
这也很有帮助。
SELECT YEAR(0), MONTH(0), DAY(0);
or
或者
SELECT YEAR(getdate()), MONTH(getdate()), DAY(getdate());
or
或者
SELECT YEAR(yourDateField), MONTH(yourDateField), DAY(yourDateField);
回答by Philippe Grondier
let's write it this way: YEAR(anySqlDate)and MONTH(anySqlDate). Try it with YEAR(GETDATE())for example.
让我们这样写：YEAR(anySqlDate)和MONTH(anySqlDate)。YEAR(GETDATE())例如尝试一下。
回答by razvangry
convert(varchar(7), <date_field>, 120)
because 120 results in 'yyyy-MM-dd' which is varchar(10)
using varchar(7) will display only year and month
example:
select convert(varchar(7), <date_field>, 120), COUNT(*)
from <some_table>
group by convert(varchar(7), <date_field>, 120)
order by 1
回答by priyanka.sarkar
I am interpreting your question in two ways.
我从两个方面解释你的问题。
a) You only need Month & Year seperately in which case here is the answer
a）您只需要分别使用月份和年份，在这种情况下，这里是答案
select
[YEAR] = YEAR(getdate())
,[YEAR] = DATEPART(YY,getdate())
, [MONTH] = month(getdate())
,[MONTH] = DATEPART(mm,getdate())
,[MONTH NAME] = DATENAME(mm, getdate())
b)
b)
You want to display from a given date say '2009-11-24 09:01:55.483'in MONTH.YEARformat. So the output should come as 11.2009in this case.
你想从某一特定日期发言权显示'2009-11-24 09:01:55.483'在MONTH.YEAR格式。所以输出应该11.2009在这种情况下。
If that is supposed to be the case then try this(among other alternatives)
如果应该是这种情况，那么试试这个（以及其他选择）
select [Month.Year] = STUFF(CONVERT(varchar(10), GETDATE(),104),1,3,'')
