[sql] Returning Month Name in SQL Server Query

Using SQL Server 2008, I have a query that is used to create a view and I'm trying to display a month's name instead of an integer.

In my database, the datetime is in a column called OrderDateTime. The lines in the query that return the date is:

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

This returns a column of years and a column of months as integers. I want to return the month names (Jan, Feb, etc). I've tried:

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

This is obviously is incorrect, as I get

Incorrect syntax near 'AS'

message. What is the proper syntax for my query?

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

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

FORMAT(date, 'MMMM') AS month

Change:

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

To:

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

This will give you what u are requesting for:

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

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

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

DECLARE @iMonth INT=12
SELECT CHOOSE(@iMonth,'JANUARY','FEBRUARY','MARCH','APRIL','MAY','JUNE','JULY','AUGUST','SEPTEMBER','OCTOBER','NOVEMBER','DECEMBER')

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

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)

SELECT MONTHNAME( `col1` ) FROM `table_name` 

Try this:

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