从SQL Server 2005中的datetime获取月和年

I need the month+year from the datetime in SQL Server like 'Jan 2008'. I'm grouping the query by month, year. I've searched and found functions like datepart, convert, etc., but none of them seem useful for this. Am I missing something here? Is there a function for this?

我需要SQL Server中的datetime(日期时间)的月份+年份，如“Jan 2008”。我按月、年对查询进行分组。我已经搜索并找到了datepart、convert等函数，但这些功能似乎都不太有用。我是不是漏掉了什么?这里有函数吗?

20 个解决方案

#1

If you mean you want them back as a string, in that format;

如果你的意思是你想要它们以那种格式作为字符串;

SELECT 
  CONVERT(CHAR(4), date_of_birth, 100) + CONVERT(CHAR(4), date_of_birth, 120) 
FROM customers

Here are the other format options

以下是其他格式选项

#2

163

select 
datepart(month,getdate()) -- integer (1,2,3...)
,datepart(year,getdate()) -- integer
,datename(month,getdate()) -- string ('September',...)

#3

Beginning with SQL Server 2012, you can use:

从SQL Server 2012开始，您可以使用:

SELECT FORMAT(@date, 'yyyyMM')

#4

Use:

使用:

select datepart(mm,getdate())  --to get month value
select datename(mm,getdate())  --to get name of month

#5

Funny, I was just playing around writing this same query out in SQL Server and then LINQ.

有趣的是，我只是在SQL Server和LINQ中编写相同的查询。

SELECT 
    DATENAME(mm, article.Created) AS Month, 
    DATENAME(yyyy, article.Created) AS Year, 
    COUNT(*) AS Total 
FROM Articles AS article 
GROUP BY 
    DATENAME(mm, article.Created), 
    DATENAME(yyyy, article.Created) 
ORDER BY Month, Year DESC

It produces the following ouput (example).

它生成以下输出(示例)。

Month | Year | Total

January | 2009 | 2

#6

In SQL server 2012, below can be used

在SQL server 2012中，可以使用以下命令

select FORMAT(getdate(), 'MMM yyyy')

选择格式(获取当前日期(),“嗯yyyy”)

This gives exact "Jun 2016"

这是准确的“2016年6月”

#7

( Month(Created) + ',' + Year(Created) ) AS Date

#8

How about this?

这个怎么样?

Select DateName( Month, getDate() ) + ' ' + DateName( Year, getDate() )

#9

That format doesn't exist. You need to do a combination of two things,

这种格式是不存在的。你需要把两件事结合起来，

select convert(varchar(4),getdate(),100)  + convert(varchar(4),year(getdate()))

#10

the best way to do that is with :

最好的办法是:

dateadd(month,datediff(month,0,*your_date*),0)

it will keep your datetime type

它将保留datetime类型

#11

I had the same problem and after looking around I found this:

我也遇到了同样的问题，我环顾四周后发现:

SELECT DATENAME(yyyy, date) AS year
FROM Income
GROUP BY DATENAME(yyyy, date)

It's working great!

这是伟大的工作!

#12

Converting the date to the first of the month allows you to Group By and Order By a single attribute, and it's faster in my experience.

将日期转换为一个月的第一个月允许您按单个属性分组并排序，这在我的经验中更快。

declare @mytable table(mydate datetime)
declare @date datetime
set @date = '19000101'
while @date < getdate() begin
    insert into @mytable values(@date)
    set @date = dateadd(day,1,@date)
end

select count(*) total_records from @mytable

select dateadd(month,datediff(month,0,mydate),0) first_of_the_month, count(*) cnt
from @mytable
group by dateadd(month,datediff(month,0,mydate),0)

#13

---Lalmuni Demos---
create table Users
(
userid int,date_of_birth date
)
---insert values---
insert into Users values(4,'9/10/1991')

select DATEDIFF(year,date_of_birth, getdate()) - (CASE WHEN (DATEADD(year, DATEDIFF(year,date_of_birth, getdate()),date_of_birth)) > getdate() THEN 1 ELSE 0 END) as Years, 
MONTH(getdate() - (DATEADD(year, DATEDIFF(year, date_of_birth, getdate()), date_of_birth))) - 1 as Months, 
DAY(getdate() - (DATEADD(year, DATEDIFF(year,date_of_birth, getdate()), date_of_birth))) - 1 as Days,
from users

#14

cast(cast(sq.QuotaDate as date) as varchar(7))

gives "2006-04" format

给“2006 - 04”格式

#15

returns the full month name, -, full year e.g. March-2017

返回完整的月份名称，-，完整的年份，如:March-2017

CONCAT(DATENAME(mm, GetDate()), '-', DATEPART(yy, GetDate()))

#16

Yes, you can use datename(month,intime) to get the month in text.

是的，您可以使用datename(月，intime)来获取文本中的月份。

#17

The question is about SQL Server 2005, many of the answers here are for later version SQL Server.

问题是关于SQL Server 2005，这里的许多答案都是针对后来的SQL Server版本。

select convert (varchar(7), getdate(),20)
--Typical output 2015-04

SQL Server 2005 does not have date function which was introduced in SQL Server 2008

SQL Server 2005没有SQL Server 2008中引入的日期函数

#18

  ,datename(month,(od.SHIP_DATE)) as MONTH_

Answer: MONTH_ January January September October December October September

答案:月_月1月1月9月10月10月10月9日。

#19

It's work great.

这是伟大的工作。

DECLARE @pYear VARCHAR(4)

DECLARE @pMonth VARCHAR(2)

DECLARE @pDay VARCHAR(2)

SET @pYear  = RIGHT(CONVERT(CHAR(10), GETDATE(), 101), 4)

SET @pMonth = LEFT(CONVERT(CHAR(10), GETDATE(), 101), 2)

SET @pDay   = SUBSTRING(CONVERT(CHAR(10), GETDATE(), 101), 4,2)

SELECT @pYear,@pMonth,@pDay

#20

-3

The following works perfectly! I just used it, try it out.

以下作品完美!我只是用它，试一下。

date_format(date,'%Y-%c')

#1