从 SQLServer2005中的日期时间获取月份和年份

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

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

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

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

Funny, I was just playing around writing this same query out in SQL Server and then 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

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!

How about this?

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

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

Use:

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

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

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

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)

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

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

gives "2006-04" format

the best way to do that is with :

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

it will keep your datetime type

Beginning with SQL Server 2012, you can use:

SELECT FORMAT(@date, 'yyyyMM')

The question is about SQL Server 2005, many of the answers here are for later version 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

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

Answer: MONTH_ January January September October December October September

In SQL server 2012, below can be used

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

This gives exact "Jun 2016"

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

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

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