17

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.

potashin
  • 44,205
  • 11
  • 83
  • 107
Neville Nazerane
  • 6,622
  • 3
  • 46
  • 79
  • if you can filter through php it's not too hard... – Joe T Oct 27 '13 at 14:21
  • 1
    [Please read this re: shorthand like `MM`](https://sqlblog.org/blogs/aaron_bertrand/archive/2011/09/20/bad-habits-to-kick-using-shorthand-with-date-time-operations.aspx). – Aaron Bertrand Oct 27 '13 at 15:44
  • THank you joe, but i am not using php right now. Also i thought its better if the sql query itself can generate the date. I am sure from the server side code after the sql is generated it is easy. – Neville Nazerane Oct 28 '13 at 08:06

5 Answers5

31
SELECT DATENAME(MONTH, GETDATE()) 
         + RIGHT(CONVERT(VARCHAR(12), GETDATE(), 107), 9) AS [Month DD, YYYY]

OR Date without Comma Between date and year, you can use the following 

SELECT DATENAME(MONTH, GETDATE()) + ' ' + CAST(DAY(GETDATE()) AS VARCHAR(2))
           + ' ' + CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS [Month DD YYYY]
M.Ali
  • 67,945
  • 13
  • 101
  • 127
  • 3
    Please stop recommending date/time shorthand. If you mean `MONTH`, type `MONTH`. [Using shorthand all the time leads to problems](https://sqlblog.org/blogs/aaron_bertrand/archive/2011/09/20/bad-habits-to-kick-using-shorthand-with-date-time-operations.aspx). – Aaron Bertrand Oct 27 '13 at 15:43
  • 1
    How do you know I didn't already up-vote? I visit lots of questions multiple times, and don't always get to everything in one shot - especially when I'm traveling. I suggest not asking people for up-votes. If you write good content it speaks for itself. – Aaron Bertrand Oct 27 '13 at 16:48
  • @AaronBertrand Take it easy pal, you dont have to have a go at me it was only a joke. How do I know you didnt upvote for it ? Clearly you didnt agree with what I did (using shorthand), Would you vote for something that you dont agree with ??? – M.Ali Oct 27 '13 at 16:54
  • 2
    It was a recommendation to not promote the use of lazy shorthand. That doesn't make the answer wrong or not useful, it's just a suggestion to make the answer better. – Aaron Bertrand Oct 27 '13 at 19:20
19

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.

Mohammad Anini
  • 5,073
  • 4
  • 35
  • 46
dr.Crow
  • 1,493
  • 14
  • 17
3

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.

TT.
  • 15,774
  • 6
  • 47
  • 88
KyleK
  • 626
  • 6
  • 7
1

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:

img1

Laxmi
  • 3,830
  • 26
  • 30
0
select datename(DAY,GETDATE()) +'-'+ datename(MONTH,GETDATE()) +'- '+ 
       datename(YEAR,GETDATE()) as 'yourcolumnname'
JIJOMON K.A
  • 1,290
  • 3
  • 12
  • 29
Sumit Srivastava
  • 1