仅从SQL日期获取月和年

I need to access only Month.Year from Date field in SQL Server.

我只需要访问一个月。SQL Server中的日期字段。

16 个解决方案

#1

125

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中，您仍然需要将时间附加到00:00:00:00 . 00.000，这与“删除”任何日和时间符号完全不同。这一天也是第一天。例如2009-10-01 00:00:00.000

#2

103

select month(dateField), year(dateField)

#3

SELECT DATEPART(yy, DateVal)
SELECT DATEPART(MM, DateVal)
SELECT DATENAME(MM, DateVal)

#4

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/howto - datetimedate/date/date/date- 2005/。

#5

datename(m,column)+' '+cast(datepart(yyyy,column) as varchar) as MonthYear

the output will look like: 'December 2013'

产出将会是:“2013年12月”

#6

There are two SQL function to do it:

它有两个SQL函数:

DATEPART()
DATEPART()
YEAR() and MONTH().
年()和()。

Refer to the linked documentation for details.

详细信息请参阅链接的文档。

#7

This can be helpful as well.

这也很有帮助。

SELECT YEAR(0), MONTH(0), DAY(0);

或

SELECT YEAR(getdate()), MONTH(getdate()), DAY(getdate());

或

SELECT YEAR(yourDateField), MONTH(yourDateField), DAY(yourDateField);

#8

let's write it this way: YEAR(anySqlDate) and MONTH(anySqlDate). Try it with YEAR(GETDATE()) for example.

让我们这样写:YEAR(anySqlDate)和MONTH(anySqlDate)。例如，尝试使用YEAR(GETDATE())。

#9

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

#10

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())

You want to display from a given date say '2009-11-24 09:01:55.483' in MONTH.YEAR format. So the output should come as 11.2009 in this case.

你想在一个给定的日期显示“2009年11月24日09:57 .483”。年的格式。所以产量应该是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,'')

#11

RIGHT(CONVERT(VARCHAR(10), reg_dte, 105), 7)

#12

Try this

试试这个

select to_char(DATEFIELD,'MON') from YOUR_TABLE

eg.

如。

select to_char(sysdate, 'MON') from dual

#13

CONCAT (datepart (yy,DATE), FORMAT (DATE,'MM'))

CONCAT (datepart (yy,DATE)，格式(日期，'MM'))

gives you eg 201601 if you want a six digit result

如果你想要一个六位数的结果，就给出201601

#14

My database doesn't support most of the functions above however I found that this works:

我的数据库不支持上面的大部分功能，但是我发现这是可行的:

SELECT * FROM table WHERE SUBSTR(datetime_column, starting_position, number_of_strings)=required_year_and_month;

从SUBSTR(datetime_column, starting_position, number_of_strings)=required_year_and_month的表中选择*;

for example: SELECT SUBSTR(created, 1,7) FROM table;

例如:从表中选择SUBSTR(创建，1,7);

returns the year and month in the format "yyyy-mm"

以“yyyy-mm”格式返回年份和月份

#15

select convert(varchar(11), transfer_date, 106)

选择转换(transfer_date varchar(11),106)

got me my desired result of date formatted as 07 Mar 2018

得到了我想要的日期格式为2018年3月07日的结果

My column 'transfer_date' is a datetime type column and I am using SQL Server 2017 on azure

我的列“transfer_date”是一个datetime类型的列，我正在azure上使用SQL Server 2017

#16

Some of the databases such as MS ACCESS or RODBC may not support the SQL SERVER functions, but for any database that has the FORMAT function you can simply do this:

有些数据库(如MS ACCESS或RODBC)可能不支持SQL SERVER函数，但是对于任何具有格式函数的数据库，您可以这样做:

SELECT FORMAT(<your-date-field>,"YYYY-MM") AS year-date FROM <your-table>

#1

125

SELECT
   DATEADD(MONTH, DATEDIFF(MONTH, 0, <dateField>), 0) AS [year_month_date_field]
FROM
   <your_table>