在 SQLServer 查询中返回月名

使用 SQL Server 2008，我有一个用于创建视图的查询，我试图显示一个 月份的名字而不是一个整数。

在我的数据库中，datetime位于名为 OrderDateTime的列中。查询中返回日期的行为:

DATENAME(yyyy, S0.OrderDateTime) AS OrderYear,
DATEPART(MONTH, S0.OrderDateTime) AS OrderMonth

这将以整数形式返回年份列和月份列。我想返回月份名称 (Jan, Feb, etc)。我试过了:

CONVERT(varchar(3), DATEPART(MONTH, S0.OrderDateTime) AS OrderMonth

这显然是不正确的，因为我得到

“ AS”附近的语法不正确

我的查询的正确语法是什么？

431415 次浏览
小开
最佳答案

This will give you the full name of the month.

select datename(month, S0.OrderDateTime)

If you only want the first three letters you can use this

select convert(char(3), S0.OrderDateTime, 0)
小开

Change:

CONVERT(varchar(3), DATEPART(MONTH, S0.OrderDateTime) AS OrderMonth

To:

CONVERT(varchar(3), DATENAME(MONTH, S0.OrderDateTime)) AS OrderMonth
小开

Have you tried DATENAME(MONTH, S0.OrderDateTime) ?

小开

This will give you what u are requesting for:

select convert(varchar(3),datename(month, S0.OrderDateTime))
小开

Select SUBSTRING (convert(varchar,S0.OrderDateTime,100),1,3) from your Table Name

小开

Try this:

SELECT LEFT(DATENAME(MONTH,Getdate()),3)
小开

In SQL Server 2012 it is possible to use FORMAT(@mydate, 'MMMM') AS MonthName

小开 
SELECT MONTHNAME( `col1` ) FROM `table_name`
小开

Without hitting db we can fetch all months name.

WITH CTE_Sample1 AS
(
Select 0 as MonthNumber


UNION ALL


select MonthNumber+1 FROM CTE_Sample1
WHERE MonthNumber+1<12
)


Select DateName( month , DateAdd( month , MonthNumber ,0 ) ) from CTE_Sample1
小开

basically this ...

declare @currentdate datetime = getdate()
select left(datename(month,DATEADD(MONTH, -1, GETDATE())),3)
union all
select left(datename(month,(DATEADD(MONTH, -2, GETDATE()))),3)
union all
select left(datename(month,(DATEADD(MONTH, -3, GETDATE()))),3)
小开 
DECLARE @iMonth INT=12
SELECT CHOOSE(@iMonth,'JANUARY','FEBRUARY','MARCH','APRIL','MAY','JUNE','JULY','AUGUST','SEPTEMBER','OCTOBER','NOVEMBER','DECEMBER')
小开

for me DATENAME was not accessable due to company restrictions.... but this worked very easy too.

FORMAT(date, 'MMMM') AS month

提交答案