Returning Month Name in SQL Server Query

Casey DiBroaggio picture Casey DiBroaggio · Apr 13, 2011 · Viewed 404.1k times · Source

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?

Answer

Mikael Eriksson picture Mikael Eriksson · Apr 13, 2011

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)