[sql] sql server Get the FULL month name from a date

How do I use sql to get the whole month name in sql server?
I did't find a way using DATEPART(mm, mydate) or CONVERT(VARCHAR(12), CreatedFor, 107).

Basically I need in the format: April 1 2009.

If you are using SQL Server 2012 or later, you can use:

SELECT FORMAT(MyDate, 'MMMM dd yyyy')

You can view the documentation for more information on the format.

Most answers are a bit more complicated than necessary, or don't provide the exact format requested.

select Format(getdate(), 'MMMM dd yyyy') --returns 'October 01 2020', note the leading zero
select Format(getdate(), 'MMMM d yyyy') --returns the desired format with out the leading zero: 'October 1 2020'

If you want a comma, as you normally would, use:

select Format(getdate(), 'MMMM d, yyyy') --returns 'October 1, 2020'

Note: even though there is only one 'd' for the day, it will become a 2 digit day when needed.

select datename(DAY,GETDATE()) +'-'+ datename(MONTH,GETDATE()) +'- '+ 
       datename(YEAR,GETDATE()) as 'yourcolumnname'

109 - mon dd yyyy (In SQL conversion)

The required format is April 1 2009

so

 SELECT DATENAME(MONTH, GETDATE()) + RIGHT(CONVERT(VARCHAR(12), GETDATE(), 109), 9)

Result is: