As you probably know, the month function runs against a date and returns numerically, giving you the number of the month of the year. For instance, if your date is in August, it will return an 8.

If you want the name of the month, you need to use DateName, which uses 2 parameters, including the date you are using. It takes 2 parameters, because it not only returns the Month name, but others also.

To get the Month name, you would use something like this:

SELECT DATENAME (dw, orderDate) as MonthName  
FROM [Northwind].[dbo].[Orders]
where orderid =10530 

The official syntax is:

DATENAME (datepart ,date )

The designated datepart parameter (in this case), ‘dw’ is for Day of Week. The other nvarchar parameter would be ‘mm’ or ‘m’ for the name of the month itself.

The rest of the datepart abbreviations mostly return integers. To see a complete list of other datepart abbreviations that may be used in the DATENAME function, click here:
DateName Datepart Abbreviations